Login failed for user ”. at System.Data.SqlClient.SqlInternalConnectionTds..ctor

Azure SQL

While connecting to the Azure SQL Database – I was getting the below error which took me hour to find out the root cause.

Login failed for user ”. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

Initially I thought it is related to the password which is sent without the SecureString however it was still not working.

Luckily after hour of research, found creating a new user with [db_owner] permission will do the work – thanks to this blog.

here are the command to run to create the SQL new user and set the required permissions:

  1. Connect with SSMS to the master database
  2. CREATE LOGIN username WITH password=’password’;
  3. Switch to the application database
  4. CREATE USER username FROM LOGIN username;
  5. EXEC sp_addrolemember N’db_owner’, N’username’
  6. Updating the connection string in the Azure portal
Create a read-only user in Azure SQL – Vincent – Technologist