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:
- Connect with SSMS to the master database
- CREATE LOGIN username WITH password=’password’;
- Switch to the application database
- CREATE USER username FROM LOGIN username;
- EXEC sp_addrolemember N’db_owner’, N’username’
- Updating the connection string in the Azure portal