PHP and Microsoft MSSQL SERVER

10Jul08

This is a solution that took me about 1 year to make it work. I proudly present the way to make php connect to an Microsoft SQL Server 2005 or SQL Server Express in all major Win OS (XP, VISTA , Widows Server).

Quick tip: use # to make MsSQL dates work!!

So here it Goes !!!!!

first of all download this library  ntwdblib.dll.

1. php.ini change value  mssql.secure_connection = On
2. replace ntwdlib.dll vesrion 200.80.194.0 to windows\system32 (needs restart), wamp\php, wamp\apache2\bin
3. add extension=php_mssql.dll in php.ini it’s already there commented
to check if function working use this
if (function_exists(‘mssql_connect’))
{
echo “Okay, fn is there”;
}
else
{
echo “Hmmm .. fn is not even there”;
}

4. Enable the TCP/IP protocol using the Surface Area Configuration Utility

Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility

Make sure the SQL Server browser is started. Note this step is optional. It is possible to set
the SQL Server instance to use a fixed IP address – but this is non-standard for named instances.

Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine.
This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
Note: In order to get things to work. You might need to completely reboot the server machine after
making the changes. There have been reports (thanks P.C.) that starting and stopping the SQL Server
and Browser software is not enough.

5. Enable All TCP/IP All IPs in SQL SERVER CONFIGURATION MANAGER –> PROTOCOLS –> (TAB) IP ADDRESSES

6. Restart SQL Server Service

7. Open wamp\php\php.ini and set the mssql.secure_connection = On (just in case, cause there are 2 php.ini
in install direcrory

8. (optional)
Right-click your wamp folder and click on Properties. Got to the security tab and click on Edit…
Now click on Add and type in ‘Everyone’ without the single quotes
Once you added ‘Everyone’ make sure you check the Full Control checkbox in the permissions list

9. (optional)
If you cannot connect to an SQL Server instance after trying all of the above, look in the registry using regedit.exe
at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp.
One of the nameValue pairs has name TcpPort and a value, which is the port that the SQL Server is
listening on (mine was set to 1030).
(sometimes is to 1030…. SET IT TO 1433)

10.Add enviroment values to System path c:\wamp\php

11.    THIS DEPENDS ON MSQL SERVER EXPRESS INSTALLATION  (deafult INSTANCE = machinename , existing INSTANCE = (machiname)\SQLEXPRESS

//$dbms_host = “(local)\SQLEXPRESS”;   //<—–  ON WINXP
$dbms_host = “machinename”;      //<——-SEE WHATS IN SQL SERVER MANAGEMENT STUDIO
$dbms_user = “sa”;
$dbms_password = “yourpassword”;
$dbms_name = “DatabaseName”;
$ms_connection = mssql_connect($dbms_host, $dbms_user , $dbms_password) or die (“*****my fault  connecting Database”);
mssql_select_db($dbms_name, $ms_connection);

12. DONT FORGET TO CHANGE WINDOWS DATE FORMAT TO the format that your application is using. (control panel, regional settings, date, date format)

13. CHANGE php.ini  and uncomment mssql.datetimeconvert = On

and you are set to go…
This is a fully tested solution in all Windows Operating Systems and in SQL Server but also in SQL Server Express. Dont be afraid to try it, it really works…

Advertisements


7 Responses to “PHP and Microsoft MSSQL SERVER”

  1. I have spent hours trying to get PHP connected to MSSQL using Apache on Vista. I tried many manual installs many times and..

    You my friend, are my hero of the month. There are only 12 ppl a year with that can possibly earn that and January sorely let me down. So you can have that month too.

    There is just one thing I found when following your instructions. The dll you state is wrong it should be ntwdblib.dll

    Many thanks!

  2. 2 worthposting

    Post Updated… thanx for your comment.

  3. 3 Estuardo Boesche

    I’ve spent 2 hours googling for a solution to this problem, after clicking about 100 links…. this is the only one that really worked !!!

    Thanks a lot !!!

    Estuardo Boesche

  4. 4 Dave

    Thanks for the good article, but I still cannot connect so I tried
    $conn=odbc_connect($data_source,$user,$password);
    Where $data_source is the system or file DSN created in datasources(ODBC) from Control panel. It worked like a charm the first time with 60 seconds of effort.

    I spent ~8 hours looking for a way to natively connect to 2005. Kudos to your success! I just offer this alternative to those people, like me, that are still having problems.

  5. 5 Geallyles

    Hello, I want to say hi everyone.
    test

  6. 6 KingNoobie

    Anyone can help me fixing a problem please?

    kingnoobie@hotmail.it ( e-mail or add me in msn )

  7. 7 Ajinugroho

    great!!

    Thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: