Enabling TCP/IP Connections to MS SQL Express
The default setup of your Windows Virtual Server's firewall allows TCP-in connections on port 1433 which is necessary to connect to your SQL databases. The problem you'll find is that when trying to establish a connection to the database from MS SQL Server Management Studio Express you'll see the following error message.
The reason for this is that the SQL database itself needs to enable TCP/IP connections. This process requires making some changes within SQL Server Configuration Manager and setting up a local user to be used to connect.
SQL Server Configuration Manager
- To make the necessary changes you need to first log into your Virtual Server. After you've logged in press the START button and list all programs. Expand the "Microsoft SQL Server 2005" folder and run "SQL Server Configuration Manager".
- Next, expand the "SQL Server 2005 Network Configuration (32 bit)" tree and click on "Protocols for SQLEXPRESS". After you've done that you'll see that TCP/IP is disabled. Right click on the TCP/IP protocol and choose the "Enable" option.
- When you choose to enable the TCP/IP protocol you will get the following message. You can just press the OK button. There are a few more steps involved to allow the external connection to the DB and once those are completed we will stop and restart the SQL service.
- Now right click on the "TCP/IP" protocol again and this time choose the "Properties" option.
- Switch to the IP ADDRESSES tab and then scroll to the bottom of the window. There you'll see a section called TCP Port. Enter 1433 as the port number and then press the APPLY button. You'll get the same warning message as before which you can also click on the OK button for.
- You can then close the "SQL Server Configuration Manager".
Creating Remote (local) User
The next step for getting a remote connection to your SQL server is setting up a user that can log in.
- Click on the START button and then view "All Programs". Expand the "Microsoft SQL Server 2005" folder and then right click on "SQL Server Management Studio Express" and choose the "run as administrator" option. This will cause a pop up to let you know that a program needs your permission to run. Just press the Continue button to proceed.
- The login window for SQL Server will pop up and will already be filled with your connection details. Press the CONNECT button to continue.
- Next expand "Security" and then right click on the "Logins" folder and choose the "New Login..." option.
- Then choose a name for your new user. In this example the new user is named "remote". You can choose any name that you like. You also need to choose the "SQL Server Authentication" option and enter a password twice. By default the "Enforce password policy" check box is checked but it is up to you to decide how you want to manage the password for the user. Leaving it checked or unchecked won't affect the connection.
- Before you click on the OK button at the bottom of the window we need to set this new users role(s). On the left hand side of the Window click on the "Server Roles" page. The user in this example is being given the "sysadmin" role. Once you've set the role that you want your user to have press the OK button.
Enabling SQL Server Authentication
After you create your local user that will be using to connect remotely you need to make sure that your SQL Database allows "SQL Server Authentication". By default it is setup to only allow "Windows Authentication". To enable "SQL Server Authentication" you need to follow these instructions.
- While connected to your server in Microsoft SQL Server Management Studio right click on the SQL EXPRESS database on the left hand side of the window and choose the "Properties" option.
- Next you need to click on the "Security" page on the left hand side of the window. Then set the Server authentication to "SQL Server and Windows Authentication mode". Once you've made the change press the OK button.
Reloading SQL Service
Now that all the changes have been made you need to restart the SQL EXPRESS service on your server for the changes to take effect. Alternatively you could restart your server. If you want to just reload the SQL EXPRESS service you would need to follow these instructions.
- Press the START button and go to "Administrative Tools" and click on "Services".
- A "Windows needs your permission to continue" message will pop up. You can just press the CONTINUE button for that.
- In the Services window scroll through the list and find the "SQL Server (SQLEXPRESS)" service. Right click on this service and choose the "Restart" option. When the restart has completed you can close the Services window.
The next step is to test connecting from Microsoft SQL Server Management Studio Express. Make sure that when you connect you have your server name set to the IP address of your Virtual server and that the Authentication is set to "SQL Server Authentication". The user and password you setup should now allow you to connect from a remote location to your SQL server.
- NOTE:* a reboot may be required if you still cannot access the server.