Linked Server from On-Premise to Azure SQL Database I am using:
SSMS-18.4
SQL Server-SQL 2019 CU3
Windows 10

I was able to create a linked server successfully from on-premise SQL 2017 to the Azure SQL database without exposing my password. 

--Read the password from text file 
DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x   

--Drop and create linked server
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AzureDB_adventureworks')
EXEC master.dbo.sp_dropserver @server=N'AzureDB_adventureworks', @droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver
 @server = N'AzureDB_adventureworks', 
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @datasrc=N'ugdemotargetserver.database.windows.net',
 @catalog=N'adventureworks';

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'AzureDB_adventureworks',
 @useself=N'False',
 @rmtuser=N'taiob',@rmtpassword=@password;
GO


But the password is not getting the correct value.  I am getting a login failure.

Some of the error message:

Login failed for user 'taiob'. 
(.Net SqlClient Data Provider) Server
Name: .\SQL2019 
Error Number: 18456 
Severity: 14 
State: 1 
Line Number: 1


I confirmed that it is reading the correct password from the file by running the following code:

DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x   
SELECT @password


I am using the same file in other places using PowerShell without any issues.

#Replace with password file location
$password = Get-Content "C:\Azure SQL Database - Where is my  SQL Agent\password.txt"


If I hardcode the password it works fine.  If I print the variable I can see the value is correct. It is not a firewall issue as I can directly connect from the same SSMS that I am running the code from.
Depending on how you create the text file, it can be in all sorts of formats.  Unicode or ANSI. With or without a Byte order Mark.  With or without trailing whitespace and line feeds, either posix or Windows style. 

JSON is simpler that way.  

For instance if you run cmd.exe

c:\&gt;echo CowboYs4EVR! &gt; password.txt


and then dump the bulk column

SELECT BulkColumn
FROM    OPENROWSET(BULK 'C:\temp\password.txt', SINGLE_blob) AS x   


You'll see

0x436F77626F79733445565221200D0A


An ANSI tring with a trailing space and trailing CRLF, which you'll need to trim, like this:

set @password = trim(replace(replace(@password, char(13),''),char(10),''))


If you write the file from PowerShell it's likely to be unicode, etc.

But if you store the password in a json file instead, SQL Server will do the file format parsing for you.  

eg something as simple as:

C:\temp&gt;echo ["CowboYs4EVR!"] &gt; password.json


and

DECLARE @password VARCHAR(MAX)
SELECT  @password = json_value(BulkColumn, '$[0]')
FROM    OPENROWSET(BULK 'C:\temp\password.json', SINGLE_CLOB) AS x