Cannot connect with Serverless SQL from Pipeline within same Synapse Workspace, SqlErrorNumber=18456

Question

Cannot connect with Serverless SQL from Pipeline within same Synapse Workspace, SqlErrorNumber=18456

Jon Cajacob 0

Hello,

I am testing Synapse with a very simple setup and I wanted to execute an SQL script from within a Pipeline against a Serverless SQL database within the same synapse workspace.

synapse2

However, when testing the connection, I get the following error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'tcp:dvajcasynapse1.sql.azuresynapse.net,1433', Database: 'master', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '...'., SqlErrorNumber=18456,Class=14,State=1, Activity ID: ....

Here are my network settings of the Synapse workspace:

synapse1

Any ideas what I am doing wrong?

Answer 1

Vinodh247 2,931

Hi

Thanks for reaching out to Microsoft Q&A.

Login failure error. If you are using azuread, try with a sql auth credentials and check. Firewall might not be an issue since you have allowed all.

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.

BhargavaGunnam-MSFT 13,411 Reputation points Microsoft Employee

2023-05-22T23:43:34.8533333+00:00

Thank you, Vinodh247

Hello Jon Cajacob,

As Vinodh suggested, please try with the SQL auth credentials and check.

As per the login failure error message, the issue seems to be credentials specified in the linked service(incorrect login credentials)

Also, please try connecting to the Serverless SQL database using a tool like SSMS with the same credentials. This can help determine if the issue is specific to the pipeline or a more general connection problem.
Jon Cajacob 0 Reputation points

2023-05-23T05:41:33.9133333+00:00

Hello all, thank you for your answers.

The solution to the problem was actually to create a Linked Service that allows to connect with the Serverless SQL database of the Azure Synapse instance.

Answer 2

Jon Cajacob 0

Thanks all for the suggestions.

The solution in the end was to create a Linked Service that allows to connect with the Serverless SQL inside the Azure Synapse instance.

BhargavaGunnam-MSFT 13,411 Reputation points Microsoft Employee

2023-05-23T18:26:04.49+00:00

Hello Jon Cajacob,

I'm glad to hear that you could resolve the issue, and I see you have downvoted our community expert's answer.

As per the login failure error message, the issue seems to be credentials specified in the linked service

so he suggested trying SQL auth to test the linked service connection.

And if you feel this is a valid response, please consider upvoting Vinodh247's Answer.

Please let us know if you have any feedback to improve the experience.

Cannot connect with Serverless SQL from Pipeline within same Synapse Workspace, SqlErrorNumber=18456

2 answers

Activity