Connection sring forAzure Synapse

Question

Connection sring forAzure Synapse

Harsh Singh 0

I am attempting to connect to Azure Synapse using the provided connection string, but I keep encountering the error message: "Could not connect to Database. Error number: 80004005 Login timeout expired." The connection string I am using is as follows:
Provider=MSOLEDBSQL; Data Source=xxxxx.sql.azuresynapse.net; Initial Catalog=MyDB; Authentication=ActiveDirectoryPassword; User ID=XXXX; Password=XXXX; Use Encryption for Data=true.

I have successfully established a connection from SQL Server Management Studio (SSMS) using the same authentication method, and the test connection with the ODBC Driver 17 for SQL Server (64-bit) also succeeds.

Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-03T10:11:14.8266667+00:00

Hi @Harsh Singh

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

It seems like you're encountering a "Login timeout expired" error when trying to connect to Azure Synapse Analytics using the OLE DB provider.

The "Timeout expired" error commonly occurs when an instance of the SQL Server Database Engine is not running, when the server's name was typed incorrectly, or when there are network problems or firewalls.

For more information, please refer: Typical causes and resolutions for the error

Please refer this thread which might help you in getting some additional insights.

https://learn.microsoft.com/en-us/answers/questions/1685417/azure-synapse-database-how-to-fix-hy000-login-fail

I hope this information helps.

Thank you.
Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-04T02:05:25.43+00:00

@Harsh Singh

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.
Harsh Singh 0 Reputation points

2024-12-04T18:28:42.76+00:00

Hello @Chandra Boorla

Thanks, that works. My bad,the server name was missing an character.

Post correcting the server name I tried to connect and now it failed with another
Error "Could not Connect to Database. Error number: 80004005 <Driver> Invalid connection string attribute,
I verified all the attributes and all are correct. Rhe only change I made is corrected the server name.

Much appreciated we ca have a corect connection string with required parameters for connectig Synapse.
Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-06T02:28:29.1066667+00:00

@Harsh Singh

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.

1 answer

Your answer

Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-03T10:11:14.8266667+00:00

Hi @Harsh Singh

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

It seems like you're encountering a "Login timeout expired" error when trying to connect to Azure Synapse Analytics using the OLE DB provider.

The "Timeout expired" error commonly occurs when an instance of the SQL Server Database Engine is not running, when the server's name was typed incorrectly, or when there are network problems or firewalls.

For more information, please refer: Typical causes and resolutions for the error

Please refer this thread which might help you in getting some additional insights.

https://learn.microsoft.com/en-us/answers/questions/1685417/azure-synapse-database-how-to-fix-hy000-login-fail

I hope this information helps.

Thank you.
Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-04T02:05:25.43+00:00

@Harsh Singh

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.
Harsh Singh 0 Reputation points

2024-12-04T18:28:42.76+00:00

Hello @Chandra Boorla

Thanks, that works. My bad,the server name was missing an character.

Post correcting the server name I tried to connect and now it failed with another
Error "Could not Connect to Database. Error number: 80004005 <Driver> Invalid connection string attribute,
I verified all the attributes and all are correct. Rhe only change I made is corrected the server name.

Much appreciated we ca have a corect connection string with required parameters for connectig Synapse.
Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-06T02:28:29.1066667+00:00

@Harsh Singh

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.

Answer 1

@Harsh Singh

Glad to hear that the server's name issue was resolved. Regarding the new error, "Invalid connection string attribute," this typically occurs when there is an unsupported or incorrectly formatted parameter in the connection string.

To connect to Azure Synapse using a connection string with Active Directory authentication, you can use the following format. Ensure that you replace the placeholders with your actual server's name, database name, user ID, and password:

Here’s a refined version of the connection string you can try:

Provider=MSOLEDBSQL; 
Data Source=xxxxx.sql.azuresynapse.net; 
Initial Catalog=MyDB; 
Authentication=ActiveDirectoryPassword; 
User  ID=XXXX; 
Password=XXXX; 
Encrypt=True; 
TrustServerCertificate=False; 
Connection Timeout=30;

For more details, please refer: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-connection-strings

I hope this information helps. Please do let us know if you have any further queries.

Thank you.

Chandra Boorla 14,675 Reputation points Microsoft External Staff Moderator

2024-12-09T01:56:16.4366667+00:00

@Harsh Singh

Following up to see if the above suggestion was helpful. 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.

Share via

Connection sring forAzure Synapse

1 answer

Your answer