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:
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:
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?