19

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?

18

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.

4
  • We had an existing account that was working and then stopped working. I had to grant connect again to it. 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? 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/… Oct 28 '16 at 10:31
7

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.

1
  • Explanation: When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.
    – 321X
    Sep 28 '20 at 18:58
3

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.

2
  • Care to explain the down vote? 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! Jan 14 '18 at 21:44
1

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

1
  • 1
    Not OP, but this was actually my issue. Thanks!
    – FembotDBA
    May 3 '20 at 19:27
0

The cause in my case was that we're using an older version of ASP.NET Identity. When you're new'ing up the IdentityDbContext, it checks for some database objects for a version check. It does this by opening a second SQL connection using the first connection's ConnectionString:

From Identity's source code :(https://github.com/aspnet/AspNetIdentity/blob/b7826741279450c58b230ece98bd04b4815beabf/src/Microsoft.AspNet.Identity.EntityFramework/IdentityDbContext.cs#L198)

        internal static bool IsIdentityV1Schema(DbContext db)
        {
            var originalConnection = db.Database.Connection as SqlConnection;
            // Give up and assume its ok if its not a sql connection
            if (originalConnection == null)
            {
                return false;
            }

            if (db.Database.Exists())
            {
                using (var tempConnection = new SqlConnection(originalConnection.ConnectionString))
                {
                    tempConnection.Open();
                    return
                        VerifyColumns(tempConnection, "AspNetUsers", "Id", "UserName", "PasswordHash", "SecurityStamp",
                            "Discriminator") &&
                        VerifyColumns(tempConnection, "AspNetRoles", "Id", "Name") &&
                        VerifyColumns(tempConnection, "AspNetUserRoles", "UserId", "RoleId") &&
                        VerifyColumns(tempConnection, "AspNetUserClaims", "Id", "ClaimType", "ClaimValue", "User_Id") &&
                        VerifyColumns(tempConnection, "AspNetUserLogins", "UserId", "ProviderKey", "LoginProvider");
                }
            }

            return false;
        }

If Persist Security Info is false (as mentioned by another answer), that this check fails (since it now tries to open a 2nd connection without providing a password).

An additional flag can be passed to the IdentityDbConte

var identityContext = new IdentityDbContext<UserModel>(config.ConnectionString, false);

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.