I am trying to use the default sql storage of synapse ws as the sink for my data, but it throws logon failure

Question

I am trying to use the default sql storage of synapse ws as the sink for my data, but it throws logon failure

Anonymous

Here is the error I am getting

Cannot connect to SQL database: 'jdbc:sqlserver://<synapse workspace name>.sql.azuresynapse.net:1433;database={Built-in}', 'Managed Identity (factory name): <synapse workspace name>'.[SQL Exception]Error Code:18456, Error Message: Login failed for user '<token-identified principal>'. ClientConnectionId:<removed-id>, error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:300)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:133)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6295)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4907)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:90)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4845)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3358)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663)

shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064)

com.microsoft.dataflow.store.mssql.CustomSqlServerDriver.$anonfun$connect$1(CustomSqlServerDriver.scala:113)

scala.util.Try$.apply(Try.scala:213)

com.microsoft.dataflow.store.mssql.CustomSqlServerDriver.getConnection$1(CustomSqlServerDriver.scala:113)

com.microsoft.dataflow.store.mssql.CustomSqlServerDriver.connect(CustomSqlServerDriver.scala:128)

org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49)

org.apache.spark.sql.execution.datasources.jdbc.connection.ConnectionProviderBase.create(ConnectionProvider.scala:102)

org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1(JdbcDialects.scala:122)

org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1$adapted(JdbcDialects.scala:118)

com.microsoft.dataflow.store.mssql.MSSQLCharacteristic.newConnection(MSSQLStore.scala:111)

com.microsoft.dataflow.store.mssql.SQLDWStore$.$anonfun$connect$1(SQLDWStore.scala:98)

scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)

scala.util.Try$.apply(Try.scala:213)

com.microsoft.dataflow.store.mssql.SQLDWStore$.connect(SQLDWStore.scala:97)

com.microsoft.dataflow.transformers.StoreDelegate.connect(StoreDefinition.scala:114)

com.microsoft.dataflow.DataflowJobFuture.$anonfun$connect$1(DataflowJobFuture.scala:1335)

scala.concurrent.Future$.$anonfun$apply$1(Future.scala:659)

scala.util.Success.$anonfun$map$1(Try.scala:255)

scala.util.Success.map(Try.scala:213)

scala.concurrent.Future.$anonfun$map$1(Future.scala:292)

scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:33)

scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:33)

scala.concurrent.impl.CallbackRunnable.run(Promise.scala:64)

java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

java.lang.Thread.run(Thread.java:750)

3 answers

Your answer

Answer 1

@Seema Nair - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Ask: I am trying to use the default sql storage of synapse ws as the sink for my data, but it throws logon failure

Solution: The issue is resolved by using/generating a Shared Access Signature (SAS) and providing explicit rights to the Synapse Workspace on the Data Lake Storage.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Ganesh Gurram 7,295 Microsoft External Staff Moderator

Hi @Seema Nair

Greetings & Welcome to the Microsoft Q&A forum! Thank you for sharing your query.

The error message you're encountering, Login failed for user '<token-identified principal>', typically indicates that the service principal does not have sufficient permissions in the Azure Synapse Analytics workspace. Here are some steps you can take to troubleshoot and resolve the issue:

Check Service Principal Credentials - Ensure that the service principal credential exists as a user in the Synapse Analytics workspace. You can create an external user for the service principal using the following SQL command:

   CREATE USER <serviceprincipal> FROM EXTERNAL PROVIDER

User's image

Grant Permissions - If the service principal credential has insufficient permissions, you may need to grant the necessary permissions. For example, to grant SELECT permissions, you can run:

   GRANT SELECT TO <serviceprincipal>

User's image

Check Managed Identity - If you are using Managed Identity for authentication, ensure that it is enabled and that the necessary permissions are granted to the Managed Identity on the Azure Synapse workspace.

Role Assignment - Make sure that the service principal has been assigned the appropriate role, such as Synapse Administrator or Synapse SQL Administrator, in the Azure portal.

By following these steps, you should be able to resolve the login failure issue.

Similar issue for reference: https://learn.microsoft.com/en-us/answers/questions/133709/login-failed-for-user

For more details refer to these documentations:

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Anonymous

2025-02-08T03:42:56.2666667+00:00

Thank you. I tried every single step mentioned and also went through the additional documentation links and created external user, provide explicit permission, etc. as mentioned. But the issue still persists.

I have a Synapse WS and by default it creates defaultstorage and defaultsqlserver

I have a dataflow to get data from a dataset (source) and I want to use a built in serverless sql pool database as sink. For sink, I am using the inline dataset type as Azure Synapse Analytics and Linked Service as the defaultsqlserver.

However, everytime I try to connect it keeps throwing this error

Cannot connect to SQL database:

'jdbc:sqlserver://<synapse workspace name>.sql.azuresynapse.net:1433; database={<serverless sql pool database name>}',

'Managed Identity (factory name): <synapse workspace name>'.[SQL Exception]Error Code:18456,

Error Message: Login failed for user '<token-identified principal>'. ClientConnectionId:<id>,

error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

...

...
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-02-10T17:52:18.2266667+00:00

@Seema Nair - Thank you for the detailed follow-up. Since the issue is still persisting even after following the steps and reviewing the documentation, let's try a few more things specifically related to serverless SQL pools in Azure Synapse Analytics.

Since you mentioned you are trying to use a serverless SQL pool as the sink, double-check that the database name you're referencing in your connection string (<serverless sql pool database name>) matches exactly the name of your serverless SQL pool. It's common to mix up the default SQL pool with the serverless pool.

Given that you're using Managed Identity for authentication, make sure the Managed Identity has the right permissions for the serverless SQL pool such as Synapse SQL Administrator.

For more details refer: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16#login-failed-for-user-username-or-login-failed-for-user-domainusername

https://learn.microsoft.com/en-us/answers/questions/1287535/cannot-connect-with-serverless-sql-from-pipeline-w

https://learn.microsoft.com/en-us/answers/questions/1519402/error-when-connecting-to-azure-sql-database-from-a

https://stackoverflow.com/questions/72793296/login-failed-for-user-token-identified-principal-microsoft-sql-server-err

I hope this information helps!
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-02-11T13:54:47.69+00:00

@Seema Nair - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-02-12T15:30:53.6366667+00:00

@Seema Nair - Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

Answer 3

Anonymous

This can be closed, I was able to address the access issue using/generating SAS and providing explicit rights to synapse ws to the data lake storage.

Ganesh Gurram 7,295 Reputation points Microsoft External Staff Moderator

2025-03-13T05:02:23.1133333+00:00

@Seema Nair - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Share via

I am trying to use the default sql storage of synapse ws as the sink for my data, but it throws logon failure

3 answers

Your answer