I was connecting using Azure Active Directory – Universal with MFA authentication.

Unfortunately my Google/Bing-fu wasn’t up to the task of finding a post with the answer. But, after a bunch of searching and testing I did manage to figure it out for myself, so that was good. Simply enough the Active Directory Admin wasn’t set for the Azure SQL Server. If you go to the Overview tab for the server you’ll see this over on the right side.

Notice that it currently shows the Active Directory Admin as Not configured. To configure it just click on Not configured or the Active Directory Admin tab under Settings. That will take you to here:

From there just hit Set Admin at the top and pick the user or group that will be the admin. Since there can only be one admin, I highly recommend using a group if this is for work. That way multiple people can have access at the same time (for those that have a multi person DBA team this is essential IMO) and because people come and go in any job. This way it’s simple enough to change the membership of the group and not have to change every Azure SQL Server any time your DBA changes. For personal use this is far less important of course.

All it really means is, “Click Execute again, and you will be fine.”

FYI you can also get the same error when connecting to an on-premises instance and it still means the same thing.

Also along those lines here is another similar error that also means the same thing:

Translated as: “Click Execute again, and you will be fine.”

You can get additional information here.

I do want to give one warning though. When you see these errors trying again will almost always re-connect you. However, unless your connection is told to go directly to the database you need (as would happen with an Azure SQL DB connection) you may end up back at your default database.

Login failed for user ”. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

This one confused me for a couple of reasons. First, the user ”. Why an empty user? That’s not really helpful. And second Server is configured for Windows authentication only.

So what the heck is going on? After a bit of searching I found the answer here.

The pertinent quote is this:

Bottom line, the application is trying to connect using Windows authentication, but using a blank/null username and password.

And I was able to reproduce the error just like the OP by using an ODBC connection with no user specified.

It seems that a blank username confused SQL as much as the error confused me.

Aren’t confusing errors fun?

Announcement
Walk Through

If you look at the bottom of the announcement you’ll also notice links to walkthroughs for

Troubleshooting Always On Issues
Troubleshooting connectivity issues with Microsoft Azure SQL Database

I’m loving this since I see a lot of connectivity issues. To start with you are asked to gather some prerequisite information. Including

• The exact error
• The SQL Error logs
• Application and System log information
• If it exists the application connection string

 
Honestly by the time you’ve collected all of that information you’re probably already going to have solved the problem. But just in case you haven’t, you move on to the checklist. The checklist is some basic troubleshooting steps. Make sure the instance is actually up and running for instance.

Now if you’ve made it through the prereqs and the checklist and you still have a problem you get to the guided walkthrough. I flipped through a few options and it seemed pretty good. Good explanations with lots of images where needed. I’m quite looking forward to trying it out for real.

I’m guessing everyone has heard of port 1433 right? Well that particular port IS special. You do not have to specifically name the instance if it is on port 1433. And since by default the default instance starts on port 1433 …

So for example if we have two instances one is the default and the other is TOtherOne. Normally we would expect to reference these like this:

But if we change the port assignments so that TOtherOne is port 1433 and MSSQLSERVER is pointing to, well, really anything else. Now we reference them like this:

If you want to read up more on how this works I would recommend reading this article on the SQL Server Browser Service.

This also allows us to do something really useful. If we have multiple instances on a single server we can alias them in such a way that each instance is called by a different server name and without an instance name. I was able to find some good instructions on how to do this here.

First open a connection window in SSMS: BOL

This is what most people see and we do have a few options right here.

1. Server type: This is, as it says, the list of server types that SSMS can connect to. I have to admit, with rare exceptions the only one I use is Database Engine. But then again I’m a fairly plain DBA with very little BI (SSIS only) and no Reporting.
   1. Database Engine
   2. Analysis Services
   3. Reporting Services
   4. SQL Server Compact
   5. Integration Services
2. Server name: Again, very descriptive. This is the name of the server to be connected to.
3. Authentication: This area is dependent on the Server type. If for instance you are connecting to a Database Engine then the options are Windows and SQL Server Authentication. If on the other hand you are connecting to a Reporting server then your options are Windows, Basic and Forms Authentication. Etc.

 
Having gone through the basic options hit the “Options>>” button.

The page is now not only larger but we have two additional tabs. Connection Properties and Additional Connection Properties.

The Connection properties tab is dependent on what “Server Type” you are connecting to. I’m only going to go over the Database Engine properties here. If you use other connection types I highly recommend reviewing what options you have.

Database Engine: BOL

1. Connect to database: This allows you to change the database you initially connect to. This overrides the default database.
   This is helpful if your default database has been dropped preventing you from easily connecting to the server or when using a contained database and a loginless user.
2. Network protocol: TCP/IP, Named Pipes etc. Allows you to be specific about what network protocol you use to connect to the server. This not only overrides the order set by SSCM (SQL Server Connection Manager) but picks one specific network protocol and if it doesn’t work the connection fails.
   I’ve used this a number of times when testing a specific protocol so that I can actually see the failure rather than have the connection fail over to another protocol.
3. Network packet size: Change the size of the network packets sent.
   I’ve never used this but I imagine it has it’s uses also.
4. Connection time-out: Change the number of seconds before a connection timeout. The default is 15 seconds.
   This is rather helpful if some network latency is making it difficult to connect.
5. Execution time-out: Change the number of seconds before an execution timeout. The default is 0 or never.
   Again I’ve never used this but I could see how it would be helpful if you are going to walk away from a long running query and want it to time out if it goes to long.
6. Encrypt connection: Forces the connection to be encrypted.
   Useful if you need the connection to be encrypted (duh).
7. Use custom color: Pick a background color for the status bar.
   I typically set this in my registered servers so I don’t mess with it here.

 
Additional Connection Parameters: BOL

This allows you to add additional connection parameters to the connection string. Personally I’ve never seen a use for this myself although Balmukund Lakhani did.

These connection options are not a constant need but something that DBAs should be aware of for when they are needed!

In this case however it’s a server that I don’t have direct access to. So now I have to find another method to get to the port number. I thought I would share the methods that I found.

First and probably the easiest. Look in the log. Open the log file that contains the last time the instance was started up. Filter on the word “Listening”. You will get rows that look like this: “Server is listening on [ 123.123.123.123 <ipv4> 1433].” This means that the IP address 123.123.123.123 is listening on port 1433. I believe the <ipv4> is a reference to the port properties entry (IP1, IP2, IPALL etc) in SSCM (see the pic above). One thing to be careful of is something like this: “Server is listening on [ 123.123.123.123 <ipv4> 2889].” Followed by “Dedicated admin connection support was established for listening remotely on port 2889.” This is an ip/port combination that is specifically for the DAC (dedicated admin connection). You won’t be able to use this port for normal usage. I also found a tip on MSSQLTips here: (http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/) that suggests basically the same thing but uses the extended stored procedure xp_readerrorlog.

Next I found a blog “Using DMVs to Find the Ports that SQL Server is Listening On.” (http://adventuresinsql.com/2010/11/using-dmvs-to-find-the-ports-that-sql-server-is-listening-on/) It uses the DMV sys.dm_exec_connections and joins it to sys.endpoints. I found I could just run the following:

SELECT distinct local_net_address, local_tcp_port 
FROM sys.dm_exec_connections

However this method does require that there be a TCP/IP connection made to the instance. And even more specifically that at least one of the connections uses the IP/Port combination that you are interested in.

You can also connect SSCM to another machine using the following instructions: (http://technet.microsoft.com/en-us/library/ms190622.aspx)
These instructions are for SQL 2012 but I’ve used them with SQL 2005 before as well.

And last but not least (of the methods I found) you could use xp_regread to read the port from the registry. I know it’s stored there (I‘ve seen it before when researching the TCP/IP properties in SSCM) but I can’t remember exactly where it is and I wouldn’t recommend the registry unless you are desperate anyway.

I could wish that there was a DMV that exposed the information in the same format that it can be found in SSCM but I wasn’t able to find one.

If you know a method that isn’t listed above please add it in the comments.

After each step if you have changed anything do a test remote connection to see if your problem is resolved.

Note: I’m abbreviating SQL Server Configuration Manager as SSCM

1. In SSCM Check to make sure that TCP/IP is enabled.  If your shop uses Named Pipes check that also.
2. If your instance is not using port 1433 then it needs the SQL Server Browser.  Here is a good description of what it does.  Either start or restart SQL Server Browser.  I’ve noticed over time that even though the Browser shows it’s started it may not be working.  Restarting it should fix this.
3. At this point it’s probably a good idea to check the application event log on the server.  Particularly if the Browser won’t start.  Obviously the solution here is to Google/Bing/Searchofyourchoice the error and then correct it.
4. Check to make sure your port is open.  You can find the port you are using in SSCM under the TCP/IP properties. To test the port it’s easiest to use telnet in my opinion.  There are a number of other products available if you prefer, but telnet is free and comes with windows.  TELNET <servername> <port>.  If it won’t connect then you probably need to check your firewall.  I’m not much good with firewalls but here is Pinal Dave’s blog on how to make the change.  Don’t forget to add the port for the Browser (1434) along with the ports for your instance.

I’ve placed these in a specific order based on experience and ease of test/fix.  Feel free to put them in whatever order you feel the most comfortable with.