CODEWITHSUNDEEP
sql-serverentity-frameworkazureazure-sql-database
Michael Vakoč
Michael Vakoč

Reputation: 37

How to connect to Azure SQL database?

I am trying to connect my application hosted in Azure to a database also hosted in Azure. I get an error please find the below

"502 - Web server received an invalid response while acting as a gateway or proxy server."

when the app is trying to query the database.

Before hosting the app and the database in Azure (the app worked on localhost) my connection string to on premise SQL server database looked like this:

Data Source=DESKTOP-LCO4GKN;AttachDbFilename=|DataDirectory|BakaAppDb.mdf;Initial Catalog=BakaAppDb;Integrated Security=True

After migrating the database to Azure and creating a user with login I entered the connection string like this:

Server=tcp:betterorgappserver.database.windows.net;Database=BetterOrgAppDatabase_Dogfood;User ID=login1user;Password=*******;

The question is how should the connection string look like? I am using entity framework in my app, if that might influence its look.

EDIT

After some time I "managed" to get error specifying that the login for user failed

Login failed for user 'login1user1'.

This is the stack trace:

    System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +347
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +78
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +191
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +154
   System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +21
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +90
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +217
   System.Data.SqlClient.SqlConnection.Open() +96
   System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<Open>b__36(DbConnection t, DbConnectionInterceptionContext c) +10
   and so on...

I guess it means I gave the privileges to the user incorrectly. So now the question is how do I add a new user with privileges to access the database?

I did it according to this article: https://azure.microsoft.com/cs-cz/documentation/articles/sql-database-manage-logins/#granting-database-access-to-a-login

Thanks

Upvotes: 0

Views: 1092

Answers (1)

Rami Sarieddine
Rami Sarieddine

Reputation: 5432

Edited: Make sure to allow the client firewall for your application from the database server

Also, you can try adding a Data Connection to your Web App: enter image description here

You can see the connection string on the portal if you navigate to the SQL Database, your connection string should look like:

Server=tcp:yourserver.database.windows.net,1433;Database=yourdatabase;User ID=yourlogin@yourserver;Password={yourpassword};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

You can view the Server admin login on the portal as wellenter image description here

Upvotes: 1

Related Questions