how to configure ms sql server 2008 for remote access

by Dr. Michale Hagenes Published 2 years ago Updated 2 years ago

To enable remote connection on SQL Server 2008

1. Enable TCP/IP protocol and set a predefined port number (1433) To enable the TCP/IP protocol for SQL Server 2008, to accept remote connection: ...
2. Add a firewall rule By default, windows don’t allow inbound traffic from 1433 port. ...
3. Stop SQL Server Browser Service Please, for security reasons disable the service SQL Server Browser. ...
4. Check SQL Server Authentication ...
5. Open a remote connection ...

To enable remote connection on SQL Server 2008

Enable TCP/IP protocol and set a predefined port number (1433) To enable the TCP/IP protocol for SQL Server 2008, to accept remote connection: ...
Add a firewall rule. ...
Stop SQL Server Browser Service. ...
Check SQL Server Authentication. ...
Open a remote connection.

Feb 17, 2012

Full Answer

How do I enable remote access to a SQL Server database?

To configure the remote access option. In Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, select or clear the Allow remote connections to this server check box. Using Transact-SQL To configure the remote access option. Connect to the Database Engine.

What is the remote access configuration option?

This topic is about the "Remote Access" feature. This configuration option is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn't be using it. If you reached this page because you are having trouble connecting to SQL Server, see one of the following topics instead:

How do I set up remote access to a server?

How to configure SQL Server browser in Windows Server 2008?

Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager. On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.

How do I make my SQL Server database accessible remotely?

To configure the Microsoft SQL Server database for remote access:Launch SQL Server Management Studio.2.In Object Explorer, right-click on your server and select Properties.Click Connections.Under Remote server connections, select Allow remote connections to this server.Click OK to save the changes.More items...

How do I connect SQL Server 2008 R2 to another computer?

To connect to the Database Engine from another computerOn a second computer that contains the SQL Server client tools, log in with an account authorized to connect to SQL Server, and open Management Studio.In the Connect to Server dialog box, confirm Database Engine in the Server type box.More items...•

How do I connect to SQL Server 2008?

Start SQL Config Manager: Click Start -> Microsoft SQL Server 2008 R2 -> SQL Server Configuration Manager.Start SQL Services: Set the SQL Server (SQLEXPRESS) and SQL Server Browser services to automatic start mode. Right-click each service -> Properties -> Go into the Service Tab.

How do I connect to a remote database in SQL Server Management Studio?

Start the SQL Server, in the dialog window for the Server name enters the name of the instance that you want to connect with. From the Authentication drop down box, select the SQL Server Authentication and for the field Login and the Password enter your credentials then click the Connect button.

How do I connect to an SQL database using an IP address?

5 Answersopen SQL Server Configuration Manager;switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS ;double-click the TCP/IP protocol;select the Yes value in the Enabled field;switch to the IP Addresses tab;find the IPAll section;clear the TCP Dynamic Ports field in that section;More items...•

Where is SQL Server network configuration?

In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration. In the console pane, click Protocols for . In the details pane, right-click the protocol you want to change, and then click Enable or Disable.

How can I configure SQL Server?

StepsInstall SQL. Check compatible versions. Choose New SQL Server stand-alone installation.... Include any product updates. ... Create a SQL database for your website. Start the Microsoft SQL Server Management Studio app. In the Object Explorer panel, right-click on Databases, and choose New Database....

How do I find the server name in SQL Server 2008?

Open up SQL Server Configuration Manager (search for it in the Start menu). Click on SQL Server Services . The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it's the default instance.

Where is SQL Server Configuration Manager?

Take the following steps to access the SQL Server Configuration Manager:Click Start.Select All Programs.Select Microsoft SQL Server 200X.Select Configuration Tools.Select SQL Server Configuration Tools.Select SQL Server Configuration Manager.

How can I access my database remotely?

Allowing a Remote Server to Access Your DatabaseLog into cPanel and click the Remote MySQL icon, under Databases.Type in the connecting IP address, and click the Add Host button. ... Click Add, and you should now be able to connect remotely to your database.

Can't connect to SQL Server instance remotely?

Show activity on this post.Launch SQL Server Configuration Manager on your VPS.Take a look at the SQL Server Network Configuration. Make sure that TCP/IP is enabled.Next look at SQL Server Services. Make sure that SQL Server Browser is running.Restart the service for your instance of SQL Server.

What is a remote SQL Server?

A remote server configuration allows for a client connected to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without establishing a separate connection.

Can't connect to SQL Server from another computer?

Can't connect to SQL Server from other computersEnable TCP / IP in SQL Server Configuration.Open Port in Windows Firewall 1433.Allow remote connections to this server.

How do I share a local database between two computers?

To share a database by using a shared folder:In a home or small business environment, share a folder with specific people. ... Make sure that Access is set to open in shared mode on all of the users' computers. ... Copy the database file to the shared folder. ... On each user's computer, create a shortcut to the database file.

How do I connect to a database from a different server?

Create the remote connectionOn your database server, as a user with root privileges, open your MySQL configuration file. To locate it, enter the following command: ... Search the configuration file for bind-address . ... Save your changes to the configuration file and exit the text editor.Restart the MySQL service:

How do I connect to a SQL Server server from one server to another?

Follow these steps to create a Linked Server:Server Objects -> Linked Servers -> New Linked Server.Provide Remote Server Name.Select Remote Server Type (SQL Server or Other).Select Security -> Be made using this security context and provide login and password of remote server.Click OK and you are done !!

Configuring Remote Access on A SQL Server Instance

To enable remote connection on SQL Server right – click on the server and select the Properties option. In the Server Properties dialog under the C...

Configure A Windows Firewall For Database Engine Access

To add a firewall exception for the 1433 port go to Programs -> Administrative Tools select the Windows Firewall with Advanced Security option and...

Configuring Remote Access on A Named Instance of SQL Server

If you using a named instance of SQL Server there are specific things you need to do to allow clients to access the SQL database remotely. As alrea...

Connecting to A Remote SQL Server Instance With ApexSQL Tools

We will show connecting to a remote SQL Server instance in an example of ApexSQL Diff. When the new project is started you need to connect to a sou...

Issues With Windows Authentication

When you try to connect to a remote server using the Windows Authentication you may encounter the following error: We will show the tip to resolve...

What permissions are granted to all users in sp_configure?

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

How to find properties of a server in Object Explorer?

In Object Explorer, right-click a server and select Properties.

Can you run sp_configure with no parameters?

How to enable remote connection in SQL Server?

To enable remote connection on SQL Server right – click on the server and select the Properties option. In the Server Properties dialog under the Connections tab check the Allow remote connections to this server option:

What port does SQL Server listen to?

As already stated, the default instance that SQL Server listens is port 1433. For a named SQL Server instance, the ports that are used to talk to the SQL Server are by default dynamic.

How to add exception for 1433 port?

To add a firewall exception for the 1433 port go to Programs -> Administrative Tools select the Windows Firewall with Advanced Security option and follow the steps: In the Windows Firewall with Advanced Security dialog click on the Inbound Rules option and select the New Rule command: In the New Inbound Rule wizard select ...

How to set TCP/IP port to 1433?

Make sure that TCP/IP protocol is enabled and right click on TCP/IP and select the Properties option. In the TCP/IP Properties dialog select the IP Addresses tab and scroll down to IPAII. If the TCP Dynamic Ports dialog box contains 0, which indicates that the Database Engine is listening on dynamic ports, delete the 0 and set the TCP Dynamic Ports to blank and TCP Port to 1433. Port 1433 is the default instance that SQL Server uses:

How to change SQL Server authentication mode?

Right-click on the SQL Server Instance and select Properties. On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode. Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.

How to restart SQL Server instance?

Right-click on the SQL Server Instance and select Restart.

What is SQL Server Browser?

SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.

How to change start mode in SQL Server?

On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.

How to enable TCP/IP?

Right-click on TCP/IP and select Enable to enable the protocol.

How to access SQL Management Studio?

When opening MS SQL Management Studio, you will see a login screen that requires the following details: 1 Server Type - Select Database Engine. 2 Server Name - You can use any address which resolves to your server, including:#N#Your account's IP address.#N#Server hostname (e.g., hgws20.win.hostgator.com)#N#Your domain name (must be pointed to our server) 3 Authentication - Dedicated Server owners can only use Windows Authentication. This option is for if you are logging in remotely without using SQL Server Authentication or if you are connecting with Remote Desktop. 4 Login - This will be your database username, which can be found in the Websites & Domains section Plesk Panel by selecting the Databases icon and selecting the database you wish to use. 5 Password: This will be your database user password.

What is the server name?

Server Name - You can use any address which resolves to your server, including: Your account's IP address. Server hostname (e.g., hgws20.win.hostgator.com) Your domain name (must be pointed to our server) Authentication - Dedicated Server owners can only use Windows Authentication.

Can you use Windows authentication on a dedicated server?

Authentication - Dedicated Server owners can only use Windows Authentication. This option is for if you are logging in remotely without using SQL Server Authentication or if you are connecting with Remote Desktop.

What is remote access in SQL Server?

The remote access option in SQL Server controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. And default value of remote access option is 1, which grants permission to run local procedures from remote servers or remote procedures from the local server. If you want to prevent this, set the option to 0.

How to login to SQL Server?

Please login into SQL Server with Windows Authentication mode or SQL Server Authentication mode. 1. In Object Explorer, right-click a server and select Properties. 2. Click the Connections node. 3. Under Remote server connections, select or clear the All remote connections to this server check box.

How to use sp-configure in SQL Server?

1. Connect to the SQL Server Database Engine with SQL SA account or Windows administrator. 2. From the Standard bar, click New Query. 3. Copy and paste the following into the query window and click Execute. This example shows how to use sp-configure to set the value of the remote access option to 0. USE 'ServerName' ;

How to connect to SQL Server?

Why is remote access important?

Remote access is the ability to get access to a SQL Server from a remote distance in order to manipulate data which are located on that SQL Server. There are two types of adjustments which must be set before connecting to the remote SQL Server. These settings are important because without them the connection to the remote SQL Server will not be ...

How to open wf.msc?

From the Control Panel choose Windows Firewall and click the Advanced settings or just type wf.msc in Search program and files from the Start menu:

What is TCP/IP in SQL Server?

TCP/IP (Transmission Control Protocol /Internet Protocol) is a set of protocols developed to allow networked computers to share resources over the network. It was developed by DARPA under the ARPANET in the early 1970s.

What port is used to connect to the default instance of Database Engine?

To connect to the default instance of Database Engine, or named instance that is the only instance installed on the machine, the TCP port 1433 is the only port that you need to specify.

When was SQL Server developed?

It was developed by DARPA under the ARPANET in the early 1970s. In order to establish a successful remote connection is to set up appropriate ports through the Window Firewall. In SQL Server there are two types of instances. First is a default instance and the second is a named instance.

When is a private network setting applied?

Private: The setting is applied when a computer is connected to a network that is identified as a private network. Public: The setting is applied when a computer is connected to untrusted public network. In the Profile dialog choose all three profiles and click the Next :

How to set remote access to SQL Server?

You can reset this configuration option by using either the Transact-SQL sp_configure stored procedure or SQL Server Management Studio. To set the option in SQL Server Management Studio, on the Server Properties Connections page, use Allow remote connections to this server. To reach the Server Properties Connections page, in Object Explorer, right-click the server name, and then click Properties. On the Server Properties page, click the Connections page.

What is remote server configuration?

A remote server configuration allows for a client connected to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without establishing a separate connection. Instead, the server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server. This server in turn passes those results to the client. When you set up a remote server configuration, you should also consider how to establish security.

How to set up a pair of remote servers?

To set up a pair of remote servers, configure both servers to recognize each other as remote servers. Most of the time, you should not have to set configuration options for remote servers. SQL Server Set sets the defaults on both the local and remote computers to allow for remote server connections. For remote server access to work, the remote ...

What is XP_MSVER?

You can use the xp_msver extended stored procedure to review server attributes for local or remote servers. These attributes include the version number of SQL Server, the type and number of processors in the computer, and the version of the operating system.

When setting up a remote server configuration, should you also consider how to establish security?

If you want to set up a server configuration to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed ...

Is sp_remoteoption supported in SQL Server?

The trusted option of sp_remoteoption is not supported in SQL Server.

Can you disable a remote server?

From the local server, you can disable a remote server configuration to prevent access to that local server by users on the remote server with which it is paired .