openrowset to connect to SQL server with a password containing single quotes Can someone help me if there is ever a way to query an Azure SQL database using openrowset with an SQL authenticated account having "single quotes" in it's password string. My statement looks like the below

(I'm querying the Azure database from on-premises)

SELECT a.*  
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj'C(D=JJ&amp;'XE6vx};','select * from table1 with (nolock)')a


I tried the below solutions

solution 1 - Dynamic SQL

declare @string varchar(max)
set @string=
'SELECT a.*  
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'''+'C(D=JJ&amp;'''+'XE6vx}";'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)


Solution 2 - Passing the password in as a parameter

declare @string varchar(max)
declare @pwd varchar(max)
set @pwd = 'Fj'''+'C(D=JJ&amp;'''+'XE6vx}'
set @string=
'SELECT a.*  
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)


They all result in the very same error (points to the password)


  Msg 102, Level 15, State 1, Line 16 Incorrect syntax near 'C'.


using a SQL authenticated account without any "single quote" in it's password works absolutely fine.

I could easily change the password to fix this issue, But still would like to know if there is ever a solution to this.
This is definitely one of those occasions when you should print out @string and see what it is actually set to...

SELECT a.*  
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'C(D=JJ&amp;'XE6vx}";','select count(*) from dbo.table1 with (nolock)')a


You can see in the printed out results above (taken from Solution 1, although 2 is identical) that you are not escaping the single quotes in your connection string and so are ending the string before the 'C' and starting it again before the '&amp;'.

You need an additional double set of quotation marks before the 'C' and after the '&amp;'.

declare @string varchar(max);
declare @pwd varchar(max);
set @pwd = 'Fj'''+'''C(D=JJ&amp;'''''+'XE6vx}';
set @string=
'SELECT a.*  
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'


This gives the following string, which should now have the required escaping of the single quotes and should hopefully work.

SELECT a.*    
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj''C(D=JJ&amp;''XE6vx};','select count(*) from dbo.table1 with (nolock)')a