How to setup MSSQL Server Express 2008

I happened to install MSSQL server express and thought of writing down some useful tips.

If you have Visual Studio 2008 installed on the system, you will need to install Visual Studio Service Pack1 before installing MSSQL Server Express. Once installed, next step is to login to the server using the SQL Server Management Studio.

Connecting to SQL Server using Management Studio.

Here are the steps to follow to connect to the server using management studio.

1. Enable TCP/IP, Named Pipes  options for sql server using SQL Server configuration Manager.

2. Step 2

Using the management studio, connect to the server.

For the server type, select Database Engine. For server name, you will need to specify the Computer Name(Host computer Name)\(Server Name) Or .\(Server Name). Otherwise you will get a nasty error message. For the authentication option, select Windows Authentication. Now, all is set to connect to the server.  If you forget to specify the Host name or use .\ in the Server name option, this is the error message you will get.

Configuring SQL Server for Username/Password authentication ( Mixed mode )

In order to access the SQL Server programmatically, it should be configured for Mixed mode authentication. When configured for Mixed mode, SQL server allows login in to the server using SQL Server authentication ( Using a username, password ).

First, we need to create a new user login, which will be granted with the SQL Server Authentication. For that, go to logins tab under security as shown below.

Right click on the logins and select new login. Select SQL Server authentication option and specify the Login name and password.

Under User Mapping option, you can select the databases, this login has access to. Under status option check whether login option is enabled.

Now you are ready to login to the SQL Server using SQL Server Authentication.

Configuring a listening port for SQL Server remote access.

In order to access SQL server programmatically, the server has to be listening on a port. Usually this port is 1433. however, when installing the SQL server, if you gave the server instance a name, it wont be listening on this port by default. Hence you need to configure it.

Go to SQL Server configuration manager and select SQL server network configurations. Select the named SQL server instance which need to be configured for remote access.

Under Protocols, select TCP/IP and select the IP Addresses Tab. Now you can configure a given IP address and port for server to listen. Here, I am configuring the 127.0.0.1 for listening. On the enabled option, select yes. On the TCP Port option, specify the port. In this case, 1433. On the TCP Dynamic Ports option, if the value is 0 remove it and click ok. SQL server service should be restarted before you can access it though 1433 port.

Now get a command line and check whether server is listening on port 1433 with command netstat –a. Alternatively you can use to telent as well.