0

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;UID=john.doe@company-company.com;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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
marritza
  • 22
  • 5
  • Long shot and I don't have synapse to test but did you try `User ID` instead of `UID` (and `Password` instead of `PWD`)? These shorter forms are from ODBC which... I'm not sure is used by the version of SQLNCLI you have (that's been kind of a whirlwind). Another thing to try is to put square brackets around the username, but I'm not sure exactly when that's getting parsed and failing. As an aside, I didn't notice any examples like this [in the official docs](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset), but they do imply that @ is supported in user names. – Aaron Bertrand Feb 21 '23 at 07:09
  • Have you read through [Using Connection String Keywords with SQL Server Native Client](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client) yet? The interesting part is: _UID: A valid SQL Server login account. UID need not be specified when using Windows Authentication._ SQLNCLI has not received any feature updates since SQL Server 2012 and doesn't really support Azure connectivity. – AlwaysLearning Feb 21 '23 at 07:27

1 Answers1

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 .

Aswin
  • 4,090
  • 2
  • 4
  • 16