Connecting to SQL Server on an Azure Virtual Machine

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 Smile – 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 –

image

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…)

About these ads

About Yossi Dahan
I work as a principal consultant in the CTO office of Solidsoft - a Microsoft partner in the UK with a strong focus on cloud, hybrid and integration based solutions. I spend my days working with both our customers and our project teams, helping them explore the possibilities that technology enables and how to derive value from them.

10 Responses to Connecting to SQL Server on an Azure Virtual Machine

  1. Yossi,

    Good article, very usefull and easy to follow.

    Thanks a lot,

    Sid Vasenszky
    Auto Care Software
    Australia

  2. Gregory Mavrogeorgis says:

    Hello,

    Good article, help me connect .
    But if i have more instances how i can connect to a specific instance?

    Thanks a lot
    Gregory
    Greece

    • Yossi Dahan says:

      Gregory – u I think it is quite unlikely you would deploy another instance of the same SQL VM onto the same cloud service, I’d love to hear why you think you should? but if there was a good reason, you’d simply create another endpoint with another external port, mapped to 1433 on the second instance.

  3. Daniel Kaminski says:

    Yossi,
    This is a great post. I have completed all the steps you mentioned but I am still unable to connect to my SQL DB in the Azure VM! Any tips on what else I could do?

    • Yossi Dahan says:

      If I had to guess Daniel I’d say that there’s a firewall that blocks 1433. you could try and add an endpoint to your VM with, say, port 80 mapped internally to port 1433 and see if that works. might not be the way you’d like to move forward but will prove the point :-)

      • Yossi, you are correct, it was a FW issue. I modified the enpoint to use 1433 for both internal and external ports and opened 1433 in the VM FW and it worked. For some reason I was not able to get it to work when the endpoint was configured to use 14333 external and 1433 internal. But I am happy the way it is working now. Thanks for you help.

        Daniel

  4. Yossi Dahan says:

    Glad you’re sorted. your problem may well be because outbound on 14333 is blocked where you’re calling from..

  5. Iqbal Khan says:

    Hi Yossi,

    it is a great article.

    I am having an issue. I am able to connect to the VM SQL Server from MS Management studio and also RDP.

    But i am not able to connect to the VM SQL server from my Cloud Services.
    In the connection string i am specifying “HOST NAME” with SQL Server Username/PWD.

    The VM-SQL Server & Could Services are under the same account.

    I want to access the VM SQL Server databse from the could services.

    Thanks, Iqbal

  6. Jose says:

    Es posible que mi aplicación cliente servidor ubicada en mi PC se conecte a un servidor en la nube en lugar de conectarse al servidor local por medio de un PIPE o alguna ruta IP?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: