CodeEase.com

After 2 articles Remote connect to SQL Server 2005 Express or SQL Server 2005 and Change SQL Server or SQL Server Express Authentication Mode, the SQL Server remote connection still have more stuff to talk about.

Yes, we need to set Port and Firewall stuff to let remote computer can access.

Doug Kennard have given a nice instruction in his site and the article name is “How to Enable Remote Connection to SQL Server 2008 Express Database”.

We can follow his guide to finish all left things that we have to finish for the database remote connection.

Some day I will give my own experience if I have free time to record all my setting steps.

When I installed SQL Server Express on my local PC, I used to select Windows Authentication mode only, but later I might have to change the Authentication mode for some reasons such as connect this SQL Server database from another computer and I don’t want to set a new windows user account for another computer to access current computer, So I have to select SQL Server Authentication mode then.

Actually in SQL Server Express, there are only 2 type of Authentication Modes:  Windows Authentication mode and SQL Server and Windows Authentication mode, there is no SQL Server Authentication mode only. So if you want to change from Windows Authentication mode to  SQL Server Authentication mode, it will be changed to SQL Server and Windows Authentication mode.

Steps:

1: In SQL Server Management Studio Express, right click on the DB Server name, then click Properties:

2: On the Security page, under Server authentication, select the new server authentication mode which you want, and then click OK :

3: In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

To restart SQL Server from SQL Server Management Studio:    In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login by using Transact-SQL (from Microsoft website)

If Windows Authentication mode is selected during installation, the sa
login is disabled and a password is assigned by setup. If you later
change authentication mode to SQL Server and Windows Authentication
mode, the sa login remains disabled. To use the sa login, use the ALTER
LOGIN statement to enable the sa login and assign a new password.
 
Execute the following statements to enable the sa password and assign a password.

      ALTER LOGIN sa ENABLE ;
      GO
      ALTER LOGIN sa WITH PASSWORD = '' ;
      GO

To enable the sa login by using Management Studio (from Microsoft website)

   1.      In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
   2.      On the General page, you might have to create and confirm a password for the sa login.
   3.      On the Status page, in the Login section, click Enabled, and then click OK.

Connection String:

Now you can get Connection string:

1: Windows Authentication (Trust Connection):

connectionString="Data Source=MyDBServerAddress\SQLEXPRESS2005;Initial Catalog=S800_DB_2010;Integrated Security=True"

2: SQL Server Authentication:

connectionString="Data Source=MyDBServerAddress\SQLEXPRESS2005;Initial Catalog=testDB;User ID=terminalDBA;Password=myPassword;

Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express do not allow remote access by default. If you want to remote connect or use another computer in your LAN, you have to configure on your SQL Server 2005 or SQL Server 2005 Express.

In this article, we will use SQL Server 2005 Express as the sample database.

If you don’t set something for remote connection, you may receive an error message. The following error message is a sampe:

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

First, I suggest that you use SQL SQL Server Authentication connection but not Windows Authentication connection (you still can) for your SQL Server Database connection. If you use Windows authentication, you should have identical accounts on both server PC and remote PC (username and password are the same), This is not good for remote database connection. Don’t you think so ?

To enable remote connections on the instance of SQL Server 2005 Express, please read the following steps:

   1. Find your Microsoft SQL Server 2005 program on the Start menu in your Windows XP or related OS, point to Configuration Tools, and then you will see the menu item SQL Server Surface Area Configuration (please see below):

   2. Click SQL Server Surface Area Configuration, then click Surface Area Configuration for Services and Connections.

   3. On the new page: Surface Area Configuration for Services and Connections page,select the database which you want to set for remote connection, in our case, we have installed 2 SQL Server Express databases, we select SQLEXPRESS2005 database, then expand Database Engine, click Remote Connections, check on the Local and remote connections radio button, then select the appropriate protocol to enable for your environment, and then click Apply.

   4. Please Click OK if you receive the following message:

   5. Return to the Surface Area Configuration for Services and Connections page, expand Database Engine, select Service, On the right side, click Stop button, then wait until the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service stops, and then click Start button to restart the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service.

(part of above text were from Microsoft website)

Other instruction info:

Firewalls

The first thing that can block a connection to SQL Server is a firewall. If you have any firewalls, make sure they are configured to allow connections to SQL Server. The default TCP port that SQL Server uses is 1433. Firewalls include McAfee, Norton, Windows Firewall which ships with Windows XP SP2, and Internet Connection Firewall (ICF) which ships with Windows 2000.

(Above text copied from here Teratrax site, more info please click to visit )