ODBC connection to Azure SQL

1
votes

I am trying to create an ODBC connection to Azure SQL. While the connection was done the connection test failed with this error: Microsoft ODBC Driver for SQL Server Version 17.05.0001

Running connectivity tests...

Attempting connection [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.

[Microsoft][ODBC Driver 17 for SQL Server]Communication link failure

TESTS FAILED!

1
sql-server-2008odbcazure-sql-database

1 Answers

0
votes

Back on September I migrated a database for a company which have a few users using a Microsoft Access application, and they were having problems getting disconnected from Azure SQL Database. The issue was happening with all logins used to create an ODBC DSN except for one login that doesn't have any issues. Here is how that login was created to avoid issues connecting to Azure SQL Database:

Run this on master database

CREATE LOGIN usrlogin WITH PASSWORD = 'askjejjj' 
GO
CREATE USER usrlogin FROM LOGIN usrlogin;
GO
ALTER ROLE dbmanager ADD MEMBER usrlogin;

On the user database do the following:

CREATE USER usrlogin FROM LOGIN usrlogin;

ALTER ROLE db_owner ADD MEMBER usrlogin;

Now use that login to create the DSN and do the test.