Allowing @ symbol in connection string for OPENROWSET from Synapse

Question

I am trying to connect to Synapse SQL database external tables (which access databricks lakehouse tables) from SQL server using openrowset

This works:

  select * from
OPENROWSET(
  'SQLNCLI', 
  'Server=test-ondemand.sql.azuresynapse.net;UID=john_doe_user;PWD=password;',
  'SELECT * FROM dbo.table;');  

but this does not work:

  select * from
OPENROWSET(
  'SQLNCLI', 
  'Server=test-ondemand.sql.azuresynapse.net;[email protected];PWD=password;',
  'SELECT * FROM dbo.table;');

It returns:

Cannot open server "company-company.com" requested by the login. The login failed.

Seems that the @ character is mistaken as variable, I tried all kinds of masking, but no success.

Anyone knows how to fix this / mask the @ character?

Answer 1

If you are using Windows authentication, SQL server does not expect password and Active Directory ID. Through the use of the OS's Windows principal key, SQL Server authenticates users. As a result, SQL Server doesn't request a password to verify a name.

select * from
OPENROWSET(
  'SQLNCLI', 
  'Server=test-ondemand.sql.azuresynapse.net;
  Trusted_Connection=yes;',
  'SELECT * FROM dbo.table;'

Refer this StackExchange Database Administrators thread SQL Server - Linked Server - Using OPENROWSET with windows integrated security .