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

Jon Cajacob 0 Reputation points
2023-05-19T06:11:44.6466667+00:00

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,364 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vinodh247-1375 11,046 Reputation points
    2023-05-19T09:39:17.4033333+00:00

    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.

    2 people found this answer helpful.
  2. Jon Cajacob 0 Reputation points
    2023-05-23T05:43:16.1266667+00:00

    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.