Sunday, April 17, 2011

How to connect MSSQL server 2005/ SQLExpress from php

Sometimes we want to connect to MSSql server from PHP running on Apache.
The following steps are required to perform a hassle free connection
I am assuming that the PHP has been installed using the winlamp so that it has copied the PHP in the Apache folder.

1.) Download ntwdblib.dll (version : 2000.80.194.0) from

2.) Copy this dll file to apache\bin. Make sure you have the windows path set to this directory.

3.) Restart the apache service by going to Control Panel->Administrative Tools->Services.

4.) Go to “SQL Server Configuration Manager”.

5.) Under “SQL Server Network Configuration” clickon “Protocols for SQLExpress”.
or “Protocols for MSSQLSERVER” (in case you are using the complete SQL server)

6.) On the right hand side,right clicked on “Named Pipes” and clicked on Enable

7.) Also, right clicked on TCP/IP and clicked on enable

steps 6 and 7 are very important. If you forget any of this the connection wont happen

8.) Restart the SQL Server Express/ SQL Server service.

9.) Uncomment the line extension=mssql in the php.ini

If you check phpinfo you should see values for mssql section

Also you need to make sure that the authentication mode is mixed. The connection wont work for windows authentication.
You can do that by

In Express Edition
Right click on the SQLSERVEREXPRESS database and then properties->Security (Choose Sql server and Windows Autherntication mode)

In SQL Server 2005
Right click on the MSSQLSERVER database and then properties->Security (Choose Sql server and Windows Autherntication mode)

Create a user by clicking on security-> Logins and create new login. Make sure you give strong password. In the UseerMapping tab give control to all the database and all databse rights

Now try connection the database seeting should be
$db1 = mssql_connect("hostname\SQLEXPRESS", username, password) in case of the SQL server express edition
$db1 = mssql_connect("hostname", username, password) in case of the MSSQL Server

This will make the connection to the database.

No comments: