By Ben Collins


2015-05-27 19:47:14 8 Comments

I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me.

I created a database called Classifier. I added my IP to the firewall rules so I could connect to the Azure db server from SSMS on my workstation. Once I was able to get connected via SSMS for administration, I tried adding a user with a password to the database, like this:

CREATE USER classifier WITH PASSWORD='thepassword'

I also added this user to the data writer and reader roles:

exec sp_addrolemember 'db_datawriter', 'classifier'
exec sp_addrolemember 'db_datareader', 'classifier'

After this, I'm able to connect to the database with these credentials from SSMS:

enter image description here

But this is where things go awry: I've tried several different connection string incantations and can't seem to get connected in a web app I'm working on. It didn't work in the Azure environment, so I'm running on localhost with a connection string to the Azure database, and it just won't connect. Here's the connection string I'm using at the moment:

<add name="Classifier" connectionString="Data Source=xxxxxxx.database.secure.windows.net;Initial Catalog=Classifier;User ID=classifier;Password=xxxxxxxxxxxxx;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient"/>

I've tried resetting the password (via SSMS) for the user and then updating the connection string; I also double-checked the password by copying it right out of this connection string and into the connect dialog in SSMS to make sure I didn't have a typo of some kind there.

I enabled auditing in the Azure db server hoping to get some details as to why it's failing, but all I get is this:

Err 18456, Level 14, State 1, Sever SQL Azure, Line 1Login failed for user 'classifier'

And this is where I'm stuck. Most of what I've been able to find by way of documentation or blogs indicate that the thing to do is look at SQL Server logs to see what the real error state is which would indicate more narrowly the nature of the failure, but since I'm dealing with Azure there's no way to do that (as far as I know).

What could cause the application to fail where SSMS (and LinqPad and Visual Studio Server Explorer, incidentally) succeeds?

4 comments

@Simon_Weaver 2018-01-14 21:53:11

This can occur if running a Powershell command containing a connection string when your password contains $. You can get around this by enclosing the connection string with single quotes - or by not storing your password in the connection string in the first place ;-)

Eg. I ran into this with Scaffold-DbContext command

https://github.com/aspnet/EntityFrameworkCore/issues/6624

@Vince Horst 2017-09-02 17:25:19

My problem was different, but related: I was trying to connect to a Azure SQL database by using SQL Server Management Studio (SSMS) with a contained user. I was getting a "Login failed for user" message in SSMS.

Solution: in the SSMS connection options for the query window I had set the "Connect to Database" to the name of the database to which I was trying to connect.

Explanation: In hindsight the reason was obvious: contained users are allowed only to connect to the database(s) in which they were created.

@Vince Horst 2017-10-03 20:09:56

Care to explain the down vote?

@Simon_Weaver 2018-01-14 21:44:47

Wasn't me but probably because your answer is kind of the opposite of the question. Still useful though, and thanks to the bad design of that SSMS dialog an easy mistake to make!

@sfm 2016-09-22 19:11:47

When switching our API to connect to an Azure database via a new contained user, we had to change our connection string to include:

Persist Security Info=True;

While I don't understand why this change was required, I wanted to post here in case it helps someone else out in the future.

We originally came to try this from this question.

@HowardvanRooijen 2015-07-16 09:58:56

We found that with contained databases / contained users you must specify:

GRANT CONNECT TO [YOUR_USER]

Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.

@Shane Courtrille 2015-07-27 18:53:43

We had an existing account that was working and then stopped working. I had to grant connect again to it.

@Korayem 2016-03-23 07:29:21

On Azure, I am getting error Cannot find the user 'myuser', because it does not exist or you do not have permission. though the username exists under Security > Logins but not MyDatabse > Security > Users. I tried CREATE USER [myuser] FOR LOGIN [myuser] WITH DEFAULT_SCHEMA=[mydb] GO but got error The login already has an account under a different user name.

@Ricker Silva 2016-06-14 21:03:31

I had a similar problem. On dev environment everything works connecting to sql Azure, but once published,it throws that error. However I can not run the command or create the user, anyone trying somethin different?

@Hiren Desai 2016-10-28 10:31:42

@dezso I'm facing similar issue. My ADO.NET and EF (DBContext) code works fine in local machine (haven't tried in Azure) But EF code implemented using IdentityDBConext fails. I'm not sure what's wrong or what features of IdentityDBConext requires access to server. Here is my original question stackoverflow.com/questions/40260720/…

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] SQLCMD: "Login failed for user" on SQL Azure, but it works with SSMS

1 Answered Questions

2 Answered Questions

[SOLVED] SSMS can't connect to Azure database

0 Answered Questions

Azure SQL database AAD user can't connect

2 Answered Questions

[SOLVED] "Login failed for user" when using admin user in SSMS

1 Answered Questions

[SOLVED] Unable to connect using Azure AD Service Principal on SQL Server

3 Answered Questions

2 Answered Questions

[SOLVED] Use connection string to connect to Azure database from SSMS?

0 Answered Questions

1 Answered Questions

[SOLVED] Connecting to an SQL Azure Database from SSMS - IP Restriction

Sponsored Content