Connecting to SQL Server on an Azure Virtual Machine
June 7, 2012 10 Comments
Not surprisingly, one of the first things I’ve done when I got access to the new Virtual Machines capability on Windows Azure is create a VM with SQL Server 2012.I used the gallery image and was up and running in minutes.
The next logical thing was to remote desktop to the machine and play around, which did and I’m glad to report it was boring – everything was exactly as I expected it to be.
Next, juts for fun, I wanted to see whether I could connect to the database engine from my laptop; I knew I won’t be able to use Windows Authentication, so the first thing to do was to create a SQL login on the server and make it an administrator. standard stuff.
I was now ready to connect, so I opened Managemenet Studio on my laptop and tried to connect to yossiiaas.cloudapp.net (the not so imaginative name I gave my instance), using the SQL login I created –
This sent management studio thinking for a while before coming up with the following error –
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Hmm….looks like a connectivity issue….errr…of course! – Virtual Machines are created by default with only one endpoint – for RDP. port 1433 will be blocked by the firewall on Azure.
Thankfully it is easy enough to add an endpoint for a running instance through the management portal, so I did.
Initially I created one that uses 1433 publically and 1433 privately, but that is not a good idea as far as security is concerned. it would be much better to use a different, unexpected, port publically and map it to 1433 privately and so I ended up using the not-so-imaginative 14333 (spot the extra 3) mapped to 1433.
This adds another layer of security (by obscurity) to my database.
With this setup I tried to connect again, using yossiiaas.cloudapp.net,143333 as the server name (note the use of ‘,’ instead of ‘:’ which is what I’d initially expected) – only to get a completely different error, this time
Login failed for user ‘yossi’. (.Net SqlClient Data Provider)
Looking at the Application event log on the server (through RDP) I could spot the real problem
Login failed for user ‘yossi’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Basically – the server needed to be configured for SQL Authentication (it is configured for Windows Authentication only by default, which is the best practice, I believe)
With this done, and the service restarted I could now connect to the database engine remotely and do as I wish.
(whether that’s a good idea, and in what scenarios that could be useful is questionable, and a topic for another post…)