Troubleshooting PHP script to access Microsoft SQL

[guest]

To query SQL databse from Website running PHP, you can use this source: mssql_connect ([ string $servername [, string $username [, string $password [, bool $new_link = false ]]]] ) to create a script. We have got many people report hey have a problem to access SQL server using the PHP script. We have collected some cases and posted. Hope that will help.

Case 1: The cleint attempting to conenct the SQL Server from the Internet and get Event ID 17836. We may have 3 fixes: creating SQL login ID (recommended), join the computer to the domain and allow anonymous connections to SQL Server 2000 or to SQL Server 2005 (don't recommend). Please refer to this post: Can't connect to SQL with Event ID 17836 - viewtopic.php?f=1&t=15755

Case 2: If you use different port, you may want to add port number, for example: mssql_connect('192.0.0.0,1400','USERNAME','PASSWORD');

Case 3: If you rec eive "Login failed for user",
- Make sure you have set up a user with "SQL SERVER authentication" who has rights to connect.
- Make sure enter correct username
- Also make sure after you install and still have problems to run PHPinfo and check that you find the below:

PHPINFO: mssql
MSSQL Support enabled

Case 4: Use the Microsoft SQL Server Driver for PHP! --

1) Download from http://tiny.cc/fkGzf

2) Place php_sqlsrv_ts.dll in your php extensions folder.

3) Enable php_sqlsrv_ts.dll in your php.ini -> extension=php_sqlsrv_ts.dll

4) Follow the documentation and api reference at http://msdn.microsoft.com/en-us/library ... 90%29.aspx

This works on apache, lighttpd, wampserver (haven't tested on IIS).

You no longer need to worry about the ntwdblib.dll or whether your web server is in FastCGI mode or not.

Absolutely simple and it connects straight away!

Just remember to use the correct server name: $serverName = "<computer name>\SQLEXPRESS";

Case 5: I didn't get it working with PHP 5.2.9 and MS SQL 2008, so I used this article http://msdn.microsoft.com/en-us/library/cc793139(SQL.90).aspx and with SQL login (not Windows authentication!) it works fine!

Case 6: By far the easiest way that I've found to connect to a SQL server is to route it through a ODBC connection.

1. Administrative Tools -> Data Sources (ODBC)
2. System DNS
3. Add... "SQL Server"

Then just use odbc_connect("sql_test", "user", "pass") instead.

Case 7: When you try to connect to SQL Server 2008 Express, you always have to name the instance you are trying to connect to, even if you installed SQL2008express as "Default" instance instead of named instance. If your instance is SQLExpress and server name is myServer, the server parameter must be: "myServer\SQLExpress". If you installed Default instance of Standard (not express) version of SQL2008 instead, you don't need to name the instance. To connect using TCP/IP, you need also to check server network configuration, enable TCP/IP (listen all: yes), then go to addresses tab and setup port 1433 in all addresses you want the server listen on (by default, port is blank and server don't listen). Libraries involved in TCP/IP are ntwdblib.dll and dbnetlib.dll.

Case 8: For my setup (Windows Server 2003, IIS 6, PHP 5.2.6, SQL Server 2005 Enterprise) the key to getting a successful connection using mssql_connect was changing PHP from ISAPI mode to FastCGI mode.

1) I downloaded and installed FastCGI for IIS 6 from Microsoft http://www.iis.net/downloads/default.as ... g=6&i=1521
2) I modified my PHP install so it runs in FastCGI mode.
3) Opened fcgiext.ini and added this to the end.

[Types]
php=PHP

[PHP]
ExePath=c:\PHP\php-cgi.exe
InstanceMaxRequests=10000
EnvironmentVars=PHP_FCGI_MAX_REQUESTS:10000

4) I added cgi.force_redirect=0 to the “; Default timeout for socket based streams (seconds)” section of PHP.ini
5) I made sure my SQL database was set to Windows authentication mode and I also switched mssql.secure_connection in php.ini to on.
6) Created an SQL login for NT AUTHORITY\NETWORK SERVICE and gave it the appropriate permissions to my database.
7) Rebooted the server

Case 9: I was trying to connect to a remote SQL2000 server (located on Internet, not on the local network), then php always returned an "Unable to connect" error or something.

After waste a lot of time, by searching among foruns, blogs and logs, I finally discovered the problem. I was monitoring the TCP connections between the server and my machine when I realized that it wasn't been done. For some reason, my Apache 2.2 was "denying" php module from doing remote connections.

So I changed the php configuration on apache to CGI mode rather than as module mode. Well, it worked fine since that.

In despite of security holes that it involves, it was the only way I've found to make that remote connection works between Apache2.2/PHP5 and MSSQL Server 2000.

Strangely, connections inside local network worked in any case (in CGI or module mode).

Case 10: do NOT use ntwdblib.dll came with PHP 4.4.x. It's not most current. The version I'm using is: 8.00.194 with 274,489 Bytes. And make sure you put this file in your system32 folder.

Case 11: My solution was to install MSSQL Server Native Client

Info:
http://msdn.microsoft.com/en-us/data/aa937733.aspx

System:
W2003 w/PHP 5.2.1 and SQL Server 2008 on Win 2008
(Should also work on 2005)

My problem was that mssql_connect() failed.

Installed the native client, restarted IIS and everything went smoothly.

Case 12:
If you try to access the SQL from the Internet, make sure the TCP port 1433 is open. To test it, run telnet IP address 1433. Otherwise, you may get failed to connect error.

[guest]

I sympathise with all users trying to make SQL Server 2005 work, but that old ntwdblib api was depracated when SQL Server 2000 was released. Newer data types and features are not available through that old api.

It seems that there are still some people that don't know, and this is my third post on the subject, but there is a Microsoft SQL Server 2005 driver for php.

It's easy to use and efficient. I don't know if there's a version for Unix servers, but the person who spent 10 hours seems to be using Windows. It's not identical to mssql, but not much different, and I think simple to port.

You download the extension from Microsoft, put the dll in your extensions folder, and enable it in php.ini (or load it dynamically with dl()).

[guest]

If your are trying to connect to a MSSQLServer 8 and got this error: Unabled to connect to: [SERVER_NAME]
and u r using Server authentication, make sure 2 set up mssql.secure_connection to Off in da php.ini

[guest]

In my case, if you get login failed, set up mssql.secure_connection to On

[guest]

I have to use sql server 2005 (another piece of sh*t from MS) at work. It takes me hours to connect this garbage. I end up find I have to enable TCP/IP under "Protocols for MSSQLSERVER". To do this, you need go to
START >> PROGRAMS >> MS Sql server 2005 >> Configuration Tools >> Sql Server configuration Manger

Click "+" sign next to "Sql server 2005 network configuration" then
click to highlight "Protocols for MSSQLSERVER" then
in right panel right click "TCP/IP" and enable it.

[guest]

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).

Now in the SQL Server Configuration Manager, under SQL Server native client configuration->Client protcols->TCP\IP, the default port is listed as 1433. It seems that this registry value is overriding the setting in the SQL Server Config manager.

I changed the port number in my call to mssql_connect() to 1030 and I was able to connect. An example string is:
<?php $database = mssql_connect('laptop2,1030', 'sa', 'password', false); ?>. Note that for the server name, it can be the machine name, the string "localhost" or an IP Address such as 127.0.0.1. You do not have to append "\SQLEXPRESS" to the end of it.

Before I did this, I enabled TCP\IP connections using the SQL Server Config Manager. I also enabled the IP address under TCP/IP to be sure it worked. In php.ini, I had the mssql.secure_connection key set to "Off" (hence why I pass the username and pasword to mssql_connect()). I enabled mixed authentication is SQL Server and granted logins to user "sa". I also put the updated ntwdblib.dll in both the system32 directory and the php directory to be sure it would work.

[guest]

I ran into another problem when I updated the version of ntwdblib.dll. I picked up version 2000.80.2039.0 from an SQL Server 2000 installation. However, when I replaced the PHP 5 provided version with this one and restarted IIS, phpinfo() showed that the mssql extension wasn't loading anymore. I couldn't see any errors reported in browser nor the system error logs.

By chance I decided to run 'php -i' on the command line. This was lucky because running it this way a system error dialog popped up a warning about a missing DLL dependency.

The problem was that the new ntwdblib.dll had a dependency on MSVCR71.DLL which couldn't be found in IIS' path. I did a search for msvcr*.dll in C:\\WINDOWS and found a copy of this DLL in C:\\WINDOWS\\Microsoft.NET\\Framework\\v1.1.4322\\. I copied it into my PHP install dir and restarted IIS. Then when I ran phpinfo() the extension showed up again.

[guest]

After struggling for 6 hours trying to fix the dreaded "Not associated with a trusted SQL Server connection." error I have finally solved it after following the suggestions presented here:
http://support.microsoft.com/default.as ... -us;839569

When you try to connect to the instance of SQL Server as an anonymous user, the anonymous connection tries to open the LSA Policy handle on the computer that is running the instance of SQL Server. By default, a Windows Server 2003 member server denies an anonymous connection attempt that tries to open an LSA Policy handle if the
TurnOffAnonymousBlock
registry value is not set to 1. Therefore, your anonymous connection is not successful. Additionally, after SQL Server receives the anonymous connection request, SQL Server calls the LookupAccountSid Windows API function to obtain the account name. Because the function is called in the context of the anonymous connection, the function call also fails if the Network Access: Allow anonymous SID/Name translation security option is not enabled.

[guest]

I was having problems connecting to a SQL cluster with mssql_connect, but any other program could connect just fine. I ended up using a PHP ADODB driver to work around the connectivity problem I was having. Later, however, I discovered my problem was a mismatched name in the server table.

To see if you have this problem, "SELECT * FROM [master].[sysservers]". If the srvname, and datasource don't match the cluster's virtual SQL name... then you will have a problem. My virtual SQL server's name was 'SQL-SERV', however in the sysservers table it was listed as 'SQLSERV'.

To remedy this problem, simply issue the following queries: "EXECUTE sp_dropserver wrongName" and then "EXECUTE sp_addserver rightName, 'local'". After running these 2 procedures, restart the server and PHP should connect just fine using the native libraries.

If it still doesn't connect, make sure you update the ntwdblib.dll file as explained in prior comments.

[guest]

I couldn't connect to a remote MSSQL database using Windows Server 2003, IIS 6, PHP 5 and mssql_connect. It worked with odbc_connect and with DSN-less connection through ADODB COM object.

The solution was to change a registry entry, as follows:
1) Open regedit.exe
2) Browse to the key named HKEY_LOCAL_MACHINE\\SOFTWARE \\Microsoft\\MSSQLServer\\Client\\ConnectTo
3) There should be one or more keys in the format "SERVER" = "LIBRARY,SERVER,PORT"
where SERVER is a NetBIOS name, Named Pipe alias or the server's IP address, and PORT is usually 1433.
4) If LIBRARY is set to DBMSSOCN, try changing it to DBNETLIB. That worked for me.
5) Restart IIS

[guest]

When connecting to a SQL 2000 DB from an Apache/PHP setup on a Windows box you must connect using a SQL user login (type=standard). If you add Windows users or groups to SQL security logins and try connecting using that login the connection will fail. If the connection works for SA but not for your login, this may be your answer (it was for me!).

[guest]

I am yet another person who had problems getting php[5] to play nicely with SQL Server 2000 [MSDE] on WinXP SP2 w/IIS. Following the notes on this page from previous users did get me closer, but I still could not connect. I was getting an error that said, in essence:

"MACHINENAME\IUSR_MACHINENAME" could not connect to the server.

Through some painstaking googeling I was able to resolve the problem by following this MSKB article:
http://support.microsoft.com/default.as ... -us;319930

It seems that my default install of MSDE did NOT "Enable Mixed Mode Authentication" which is required for this kind of connection. To make matters worse, the only way to change this with MSDE is to go mucking with the registry (which always freaks me out a bit). The above article explains how to do this.

[guest]

I was having problems connecting a IIS6 Webserver on a 2003 windows box to a MSSQL Installation running windows 2000. After suspecting ntwdblib.dll I tried updating the file but with no luck. Finally I figured out the problem by noticing that in addition to a copy of the file living in the windows directory there was one in the PHP directory as well (from the zip package). Although I had an up to date version of the file in my windows directory the one in the php directory was getting read instead. I simply removed the file from my php installation directory and all worked well.

[guest]

If you intend to access MS SQL server remotely, you need to install SQL Server Client Tools on the system which hosts your PHP code.

[guest]

I don't see it mentioned here, but if you are using SQL Server 2000, then you should use a comma instead of a colon to specify the port, for example:

$sql = mssql_connect ("192.168.1.2,1433", "username", "password") or die ("Could not connect to database: ".mssql_get_last_message());

instead of:

$sql = mssql_connect ("192.168.1.2:1433", "username", "password") or die ("Could not connect to database: ".mssql_get_last_message());