Use SQL Server Management Studio to connect remotely to an SQL Server Express instance hosted on an Azure Virtual Machine

Question

Initial Attempt  I have an Azure VM with Windows Server 2012  on which I just installed SQL Server 2012 Express Database Engine component  Then  I followed the instructions here to connect remotely with SQL Server Management Studio         Create a TCP endpoint for the virtual machine   Open TCP ports in the Windows firewall   Configure SQL Server to listen on the TCP protocol   Configure SQL Server for mixed mode authentication   Create SQL Server authentication logins   Determine the DNS name of the virtual machine   Connect to the Database Engine from another computer      After step seven I received 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    What else do I need to configure before connecting remotely    Troubleshooting  I have been following the troubleshooting instructions here  Each blockquote below is a step described that that link      Confirm the instance of the SQL Server Database Engine is installed   and running    Done  We installed SQL Server Express  and it is running as a named instance called SQLEXPRESS       If you are attempting to connect to a named instance  make sure the   SQL Server Browser service is running    Done  We followed the steps here to turn on the SQL Server Browser service      Get the IP Address of the computer    Done  We will use these later for testing connectivity and maybe for setting up a static port for SQLEXPRESS   10 241 62 155  fe80  45c 8c29 e19f f78b 15     Get the TCP port number used by SQL Server    Done  The SQL Server Management Studio server logs showed that the server was listening on port 49169      Enable Protocols   Done  We had already enabled TCP IP in the configuration manager  but we restarted the SQL Server Service just in case      Testing TCP IP Connectivity   Done  We used tcping exe to test connectivity  cmd ping doesn t work quickly with Azure   We were able to connect to port 80    tcping exe buddha cloudapp net   successful tcping exe buddha cloudapp net 80   successful      Testing a Local Connection   Done  We used sqlcmd exe from the command prompt and were able to connect locally via TCP with a username and password    sqlcmd -S Buddha SQLEXPRESS  success via shared memory protocol  sqlcmd -S tcp Buddha SQLEXPRESS  success via TCP  sqlcmd -S tcp Buddha SQLEXPRESS -U sa -P   success via TCP with username  sqlcmd -S tcp 10 241 62 155 SQLEXPRESS -U sa -P   success with internal IP       Opening a Port in the Firewall   We opened the port on which we SQLEXPRESS listens  The server logs  above  showed that SQLEXPRESS was listening on port 49169  but this is just one of many dynamic ports  and we wanted to set up the static port 1435    Use WF msc to create an inbound TCP rule for port 1435  Use Azure Management Portal to create a TCP endpoint for port 1435    The troubleshooting instructions also say      If you are connecting to a named instance or a port other than TCP   port 1433  you must also open the UDP port 1434 for the SQL Server   Browser service    Since we are connecting SQLEXPRESS  a named instance   we needed to open port 1434 for UDP    Use WF msc to create an inbound UCP rule for port 1434  Use Azure Management Portal to create a UDP endpoint for port 1434   Further research about connecting to named instances revealed dynamic port issues  The reason why we are using port 1435  static  instead of port 49169  one of many effective options       Instances of SQL Server Express  SQL Server Compact  and named   instances of the Database Engine use dynamic ports  To configure these   instances to use a specific port  see Configure a Server to Listen on   a Specific TCP Port  SQL Server Configuration Manager   and   here    Done  We went to SQL Configuration Manager   SQL Server Network Configuration   Protocols for SQLEXPRESS   TCP IP  we did the following   Protocol Tab   Listen All   NO   IP Addresses Tab   for each listed address   Enabled   Yes TCP Dynamic Ports   Blank  delete the zero  TCP Port   1435  or your choice    After restarting the SQLEXPRESS service  we again looked in the SQL Server Management Studio logs  and found that the Server is Listening on port 1435    Hooray      Testing the Connection   Done  We opened SQL Server Management Studio on our local  non-Azure  computer and connected     buddha cloudapp net 1435 OR buddha cloudapp net SQLEXPRESS sa password   Success

User · Accepted Answer

Here are the three web pages on which we found the answer. The most difficult part was setting up static ports for SQLEXPRESS.

Provisioning a SQL Server Virtual Machine on Windows Azure. These initial instructions provided 25% of the answer.

How to Troubleshoot Connecting to the SQL Server Database Engine. Reading this carefully provided another 50% of the answer.

How to configure SQL server to listen on different ports on different IP addresses?. This enabled setting up static ports for named instances (eg SQLEXPRESS.) It took us the final 25% of the way to the answer.

User · Answer

The fact that you re getting an error from the Names Pipes Provider tells us that you re not using the TCP IP protocol when you re trying to establish the connection  Try adding the  tcp  prefix and specifying the port number   tcp name cloudapp net 1433

User · Answer

I too struggled with something similar  My guess is your actual problem is connecting to a SQL Express instance running on a different machine  The steps to do this can be summarized as follows    Ensure SQL Express is configured for SQL Authentication as well as Windows Authentication  the default   You do this via SQL Server Management Studio  SSMS  Server Properties Security In SSMS create a new login called  sqlUser   say  with a suitable password   sql   say  Ensure this new login is set for SQL Authentication  not Windows Authentication  SSMS Server Security Logins Properties General  Also ensure  Enforce password policy  is unchecked Under Properties Server Roles ensure this new user has the  sysadmin  role In SQL Server Configuration Manager SSCM  search for SQLServerManagerxx msc file in Windows SysWOW64 if you can t find SSCM  under SQL Server Network Configuration Protocols for SQLExpress make sure TCP IP is enabled  You can disable Named Pipes if you want Right-click protocol TCP IP and on the IPAddresses tab  ensure every one of the IP addresses is set to Enabled Yes  and TCP Port 1433  this is the default port for SQL Server  In Windows Firewall  WF msc  create two new Inbound Rules - one for SQL Server and another for SQL Browser Service  For SQL Server you need to open TCP Port 1433  if you are using the default port for SQL Server  and very importantly for the SQL Browser Service you need to open UDP Port 1434  Name these two rules suitably in your firewall Stop and restart the SQL Server Service using either SSCM or the Services msc snap-in In the Services msc snap-in make sure SQL Browser Service Startup Type is Automatic and then start this service   At this point you should be able to connect remotely  using SQL Authentication  user  sqlUser  password  sql  to the SQL Express instance configured as above  A final tip and easy way to check this out is to create an empty text file with the  UDL extension  say  Test UDL  on your desktop  Double-clicking to edit this file invokes the Microsoft Data Link Properties dialog with which you can quickly test your remote SQL connection

[sql-server] Use SQL Server Management Studio to connect remotely to an SQL Server Express instance hosted on an Azure Virtual Machine

Examples related to sql-server

Examples related to azure

Examples related to remote-access

Examples related to ports