2

I am trying to connect to an Azure SQL database via SSMS. The connection string supplied by Azure is:

Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=DATABASE_NAME;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

So, first of all I'm not sure if I should include ALL of

tcp:SERVER_NAME.database.windows.net,1433

in the server name when connecting via SSMS. Also, the username is an email address. Let's say it's:

foo@bar.com

So, this is what I tried:

ServerName: SERVER_NAME.database.windows.net
Login:      foo@bar.com@SERVER_NAME.database.windows.net
Password:   [password]

At first when I tried, I was told that the IP was not allowed. So I added the IP to the server firewall settings. But now, I get this:

Login failed for user foo@bar.com

I am using the same UserID and Password that I use to log into the Azure portal. And it's the UserID that is specified in the Access Control window.

I am working from home, but I have also tried while on my company's VPN. Neither work.

Is there something else I'm doing wrong?

closed as off-topic by LowlyDBA, Erik Darling, RDFozz, Mr.Brownstone, Vérace Dec 1 '17 at 23:17

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Erik Darling, RDFozz, Mr.Brownstone, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Voting to close since the issue was setup related and user didn't have actual permissions. – LowlyDBA Dec 1 '17 at 19:14
2

Pretty sure the problem is in the username. When you connect to an Azure SQL Database instance, foo@bar implies that you're connecting as the login "foo" to the instance "bar.database.windows.net".

The credentials you use when logging into the Azure management portal won't work when you're using SSMS.

Verify that you've set up a SQL Database login, and use that login. The admin account is set up in the Azure portal - you can verify the login name by clicking "Properties" on the server instance.

Regular logins/users are set up as 1) logins on the database server, or 2) as contained users in each database (if you have enabled containment), or 3) in Active Directory (if you've configured one).

  • ok, thanks! I was under the impression that adding @server after the email would solve the problem. That's why I was trying foo@bar.com@SERVER_NAME.database.windows.net. And also, I can not see any way, via the Azure portal, to add a Database user. Any hints would be appreciated!! – Casey Crookston Nov 30 '17 at 23:17
  • If memory serves (not at my computer right now), you can reset the admin password from the portal. Look for the support/troubleshooting tab. Regular users can then be set up in T-SQL using that login. – Daniel Hutmacher Nov 30 '17 at 23:22
  • "Regular users can then be set up in T-SQL using that login" Yes, but you have to actually be logged in to do this, and I can't even log in!! – Casey Crookston Nov 30 '17 at 23:31
  • In the Azure portal, click the Azure SQL Database instance, go to the "Overview" tab. On the top bar, the fourth button from the left is called "Reset password". See if that works for you. The login name of the admin is displayed just below, under "Server admin". – Daniel Hutmacher Dec 1 '17 at 12:20
  • ok, I will give that a shot. But, I just used this same UserID and Password to log into the Azure portal. So I'm assuming I have it right. Or, are the creds for logging into the Portal and logging into the Sql Server not the same? – Casey Crookston Dec 1 '17 at 15:29
1

Bah. This server and elastic pool was created by my boss. I got permission from him to wipe it out and start over. When I created it myself, I was able to log in from SSMS no problem.

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