Database Administration
sql-server azure-sql-database linked-server sql-server-2019
Updated Sat, 18 Jun 2022 09:46:34 GMT

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.

Solution

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:\>echo CowboYs4EVR! > 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>echo ["CowboYs4EVR!"] > password.json

and

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





References