How to: Enable Remote Connections on SQL Server
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
To enable Microsoft code name “Quadrant” to connect to data on a remote computer, you must also configure the remote computer to accept requests from “Quadrant”. The following steps give a quick guide to setting up the remote computer.
Grant User Access
To allow access to users in a Windows domain
Open the SQL Server Management Tool.
Log on to the instance of SQL Server. This step presumes that you are the administrator of the computer and the instance of SQL Server.
In Object Explorer, expand the Security node.
Right-click Logins and click New Login....
Type the name of the user to allow by using the domain\username format.
Click Search and use the dialog to confirm that the user is a Windows authenticated user. Then close the New Login dialog.
In Object Explorer, expand the Databases node.
Expand the node of the database that you want to grant access to.
Expand the Security node of the database.
Right-click the Users node and click New User.
In the User name box, type in a name for the user.
In the Login name box, type the name of the user by using the domain\username format.
In the Role Members list box, select the role that you want to grant the user for the database. Common options are db_dataread and db_datawriter.
Enable a Port
This is a one-time procedure that you perform on the computer that hosts the instance of SQL Server to allow access to the computer through a specific port. The default port set by SQL Server is 1433. If you change the default, follow the directions below and change the port number as appropriate.
To enable port 1433 on Windows Vista
On the Start menu, click Control Panel.
Under Security, select Allow a program through Windows Firewall.
If prompted, click Continue. This presumes you are the administrator of the computer.
In the Windows Firewall Settings dialog, click Add Port.
In the Name box, type a name, such as SQL Server Port.
In the Port number box, type 1433. Use the default protocol TCP.
Click OK.
To enable port 1433 on Windows 7
On the Start menu, click Control Panel.
Click System and Security.
Click Windows Firewall.
Click Advanced Settings.
In the Windows Firewall with Advanced Security on Local Computer dialog, click Inbound Rules.
In the Actions panel, click New Rule….
In the New Inbound Rule Wizard, click Port, and then click Next.
In the Specific local ports: box type 1433. Ensure that the (default) TCP radio button is selected and then click Next.
Ensure that the (default) Allow the Connection radio button is selected and then click Next.
Clear or select the Domain, Private, and Public checkboxes as appropriate, and then click Next.
In the Name box, type an appropriate name, such as SQL Server Port, and then click Finish.
Set a Protocol
This is also a one-time procedure that you perform on the computer that hosts the instance of SQL Server to indicate which protocol to use when communicating with remote clients. This procedure uses the TCP/IP protocol.
To set the protocol
Open the SQL Server Configuration Manager application. This is found in the Configuration Tools folder of the Microsoft SQL Server 2008 folder.
Expand the SQL Server Network Configuration Manager node.
Expand the SQL Server Network Configuration node.
Click Protocols for MSSQLSERVER.
Right-click TCP/IP and click Enable.
After setting the protocol you must restart the SQL Server service.
To restart the SQL Server service
In the SQL Server Configuration Manager application, click the SQL Server Services node.
Right-click SQL Server (MSSQLSERVER) and click Restart.