0

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.

1

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   
| improve this answer | |
0

I have just reproduced your scenario and in my environment, it works. The only issue I could think of might be the reading of the text file. Could you maybe:

  • try changing from SINGLE_BLOB to SINGLE_CLOB to ensure that it is read as a varchar(max) and not a varbinary?
  • make sure that there are no whitespaces in your password string?
  • try setting the password directly to the variable @password like this
DECLARE @password VARCHAR(MAX)
SELECT  @password = '....'

hth

| improve this answer | |
  • SINGLE_CLOB does not solve my problem. Checked for no whitespace. The direct setting works but I do not want that. – SqlWorldWide Mar 29 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.