14

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?

15

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.

  • We had an existing account that was working and then stopped working. I had to grant connect again to it. – Shane Courtrille Jul 27 '15 at 18:53
  • 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. – Korayem Mar 23 '16 at 7:29
  • 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? – Ricker Silva Jun 14 '16 at 21:03
  • @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/… – Hiren Desai Oct 28 '16 at 10:31
5

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.

2

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.

  • Care to explain the down vote? – Vince Horst Oct 3 '17 at 20:09
  • 1
    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! – Simon_Weaver Jan 14 '18 at 21:44
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.