mssql_connect

add a note User Contributed Notes

grv575 at hotmail dot com
04-Nov-2006 11:41


Getting IIS working with SQL Server and PHP:

1. Put the updated ntwdblib.dll (version 8.00.194) in your C:\php5 directory (which should be in your PATH environment variable)

2. Set windows environment variable PHPRC=c:\php5

3. In php.ini: uncomment the 'extension=php_mssql' line and set the extension dir: extension_dir = "c:\php5\ext"

4. Under windows folder options uncheck the use simple file sharing checkbox

5. Make sure NTFS permissions are set properly for your web files under C:\Inetput\wwwroot for anonymous web access: IUSR_<COMPUTERNAME> (and IWAM_<COMPUTERNAME> if using the php CGI exe instead of the ISAPI extension under IIS).

6. Enable anonymous web access under IIS Directory Security

7. Use SQL Server and Windows Authentication mode for SQL Server (then create an SQL Server Login using SQL Authentication and a User mapping to that Login with db_reader role membership)



Note that using fast_cgi and getting "No input file specified." can be solved by giving permission to IWAM_<COMPUTERNAME>, since it must launch a CGI process to render the file (bad generic error message...).

jishan dot org at gmail dot com
29-Oct-2006 05:47


Please note you must enable TCP/IP and Named Pipes Protocol in MS SQL Server. After enabling those protocols, PHP can connect directly to MSSQL Server.

29-Oct-2006 07:45


Also note you NEED to specify the php directory as the windows environment variable 'PHPRC' if you're using php in IIS as an ISASPI extension:



e.g. System variables:



Variable: PHPRC

Value: C:\php5



(Set this under My Computer properties -> Advanced -> Environment Variables)



Otherwise the ISASPI extension will use the compiled default of C:\WINDOWS.

jcastromail at yahoo dot es
15-Oct-2006 09:53


For sql server 2005 express using a local connection



1) "Update" the ntwdblib.dll  2000.80.194.0.



2) Sql Server must allow mix mode conecction.



3) mssql_connect ('(local)\SQLEXPRESS', 'sa', '****'); 



*** = password



It works like a charm

kamikazie at comcast dot net
24-Sep-2006 04:19


Ok, so after many many many hours of searching (somewhere around the ballpark of 2 weeks, on and off) and reading these pages, I've found the answer to my unspoken question (although there was much cursing).



Background:



A Windows Server 2003 box running as a router and a webserver using IIS6 and the latest PHP 5 as of 23 Sep 2006.



A Windows Server 2003 box running SQL 2000 Enterprise with one instance



Using a named pipe, the code to connect was:

"$connection = mssql_connect("\\.\pipe\MSSQL$instance\sql\query", "user", "pass") or die("Message"); "



I recieved the error that $instance was not a defined variable, so I changed it to

"\\.\pipe\MSSQLinstance\sql\query"

both on the connection string and the SQL server



When passed on to the server, I still recieved connection errors, so I changed it again, this time to:

"$connection = mssql_connect("\\\\.\pipe\\MSSQLinstance\\sql\\query", "user", "pass") or die("Message"); "



With this new code, I recieved a successful connection, but a bad login saying that my IUSR_ account did not have permissions on the database, although I used a separate username in the connection string.



Using SQL, I setup windows authentication for the IUSR_ account and I was able to establish a connection and successfully run a query.



I still can't find out why it was trying to pass the IUSR_ account as the login though.  If anyone has an answer drop me a line.



Now using windows groups it should be easy to create a login page that uses groups to connect to databases rather than setting up each user individually.



I just wanted to share with everyone my long awaited success story.

sbrady shift-2 allencountyohio d0t org
08-Sep-2006 11:40


Needed to set up M$SQL connection in Linux.  Used freetds and php-mssql RPMs from phprpms.sourceforge.net.  Server is M$SQL 2k5.  /etc/freedts.conf is:

[recorderlive]

   host = 10.10.20.3

   port = 1433

   tds version = 8.0



Then

<?  

$link=mssql_connect("recorderlive","ALLEN\sbrady","password");

?>

damartman (tradestudios.com)
24-Aug-2006 03:29


I am running MS SQL Server 2005 Workgroup Edition on Windows 2003 with PHP 5.  I could not connect to a MS SQL database using mssql_pconnect(); until I read a post from ashraf (aat) ametry.com on 01-May-2006 01:25.  However, my fix was simply to:



1. Replace the ntwdblib.dll with the one from http://webzila.com/dll/1/ntwdblib.zip in my c:\php5 folder.



2. Restart IIS



REASON: The ntwdblib.dll should be version 2000.80.194.0, and not version 2000.2.8.0 that PHP 5 ships with.



Thank you ashraf for posting your note, however, changing the php.ini file value mssql.secure_connection = On did not work.  I left it mssql.secure_connection = Off and that worked (the default to connect through NT/Windows Authentication)

munckfish.net
14-Jul-2006 01:55


Further to the following posts on Windows 2003 + PHP5 + SQL Server Express.



http://php.net/manual/en/function.mssql-connect.php#58787

http://uk2.php.net/manual/en/function.mssql-connect.php#61971



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.

Steve H
01-Jun-2006 12:25


To connect to SQL Server 2005 Express on Windows, do it like this:



mssql_connect ('localhost,1433', '[redacted]', '[redacted]');



localhost

localhost\SQLEXPRESS

localhost\1433

localhost:1433



will not work on Windows.



localhost,1433



does work.



YMMV on other OS's; try each.



Also make sure your TCP/IP Properties -> IP Addresses are correct under SQL Server Configuration Manager.

brijeshmathew at yahoo dot com
16-May-2006 03:58


I had problems connecting to MSSQL Server on port 1433 using PHP 5 on Windows 2003 Server. 

Whenever i tried to connect to database on a remote/local server, php tried to connect using named pipes, instead of connecting on 1433. If i disable Named pipes on the Server, the connection fails. 

As mentioned in one of the posts,from the \\Windows\\system32\\ folder, i copied and old ntwdblib.dll (dated 2000) to the php folder, and overwrote the newer file, dated 2005, that comes with php5 and suddenly, everything starts working. Php connects to MSSQL Database on port 1433 without problems.

ashraf (aat) ametry.com
02-May-2006 04:25


For me the fix was both:

1.Set in the php.ini file

mssql.secure_connection = On 

2.Replace the ntwdblib.dll with the one from http://webzila.com/dll/1/ntwdblib.zip (I actually rename the one in system32 to .old and copied the new one in the c:\php folder)

sveta at microbecal dot com
16-Feb-2006 07:19


As described here: http://php.net/manual/en/function.mssql-connect.php#58787 MSSQL Express Edition does not work with ntwdblib.dll bundled with PHP. But if you have not installed MSSQL Server2000, you can find correct ntwdblib.dll here: http://webzila.com/dll/1/ntwdblib.zip or try to find it from main page (http://www.webzila.com) if link is not work.

Charles Lowe
07-Jan-2006 01:11


I found this message in the notes below:



"I spent about a day until get working mssql_connect() function. I tried to connect PHP to MSDE. The solution is Server Network Utility (it is not installed on MSDE + Client tools configuration, only on SQL Server Standard/Enterprise). The necessary file SVRNETCN.EXE exists on the installation disk."



This actually fixed my problem that I was running into on a Windows Server 2003 SP1, IIS 6.0, PHP 4 and MSDE 2000 SP3.  I copied the SVRNETCN.EXE and SVRNETCN.DLL (located on the SQL Server 2000 Standard CD folder at: x86\binn) to the C:\Program Files\Microsoft SQL Server\MSSQL\Binn directory on the SQL server.



I ran the SVRNETCN.EXE from the local folder and "Enabled" the TCP/IP and Named Pipes protocols.  With a quick MSSQLSERVER service restart, everything ran perfectly after that.



Hope this helps.

gwkeeper at eljoy dot xxx
14-Dec-2005 12:03


I'm running PHP 5.x.x on IIS5 + Windows 2000 server and connecting to remote SQL Server 2000 with error : Login failed for user (sa). Reason: Not associated with a trusted SQL Server connection. (severity 14).



I found that even though I had "SQL and Windows Authentication" selected, and mssql.secure_connection = On in php.ini it did not work. It was due to another software instalation. 



For me was solution to install SP4 on MS SQL 2000 and it started to work pretty well.

shrockc at inhs dot org
04-Dec-2005 07:52


PHP 4.4.1 - IIS/6.0 - Windows 2K3 SP1 - MSSQL 2K connection problem



mssql_connect() refused to connect on 1 machine (IIS/6.0 on Windows Server 2003SP1), but connected okay on 2 other machines (IIS/6.0 both on Windows 2003).  One of those 2 machines had MS SQL Server 2000SP3 installed, the other did not -- neither had problems connecting to another, different Windows 2003 server running SQL2000 SP3.



I fixed the problem on the broken Windows 2003SP1 server by replacing the bundled ntwdblib.dll in my php installation directory with the version of the .dll that I found in c:\windows\system32 on a system with MS SQL 2000 installed.  I did not copy the .dll to c:\windows\system32 on the Web Server, just to the php install directory.



After capturing the packets going between the web server and the database server on both configurations, I discovered that the broken system was trying to authenticate using SMB; the working configuration was using the MSSQL TDS protocol.  Another consideration might be that in the working configurations, IIS was running as a domain user, while the broken configuration was running under the local IUSR account.

lbowerh at netscape dot net
16-Nov-2005 11:32


The recently released SQL Server 2005 Express will work with MSSQL functions. Here are some caveats:



1) The version of ntwdblib.dll supplied with PHP will not work with Server 2005 (Win32 only). You must use a newer version. The version supplied with SQL Sever 2000 (2000.80.194.0) seems to work well.

2) You must set up mssql_connect to reference the server AND the instance name, eg:

mssql_connect("MyPC\SQLEXPRESS","MyUser","MyPwd");



Although the MSSQL functions work, you might want to consider using the new 'pdo_odbc' (PHP 5.1) with SQL Server 2005 Express for any new code you are writing.

dave at phantomfish dot co dot uk
20-Oct-2005 09:48


I have spent the last 2 days trying to get PHP to connect to MSDE on Windows 2003 Web Edition.



Although initally PHP would connect to the database without a problem, it would then refuse to connect, hitting F5 enough times would eventually connect to the database. As you can imagine this intermittent problem was hard to trace.



I was using PHP 5.0.5 (although 5.0.4 has the same problem) on a Windows 2003 Web Edition SP1 server, and was trying to connect to MSDE Rel A.



Solution:

~~~~~~~~~



Firstly MSDE needed to be installed with the following arguments: SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0.



Secondly it was necessary to delete ntwdblib.dll from c:\php



Thirdly I downloaded the SQL 2000 evaluation and installed only the client tools on a seperate PC (Ent. Manager will now connect to the database).



Lastly I copied the version of ntwdblib.dll from the PC with Ent. Manager on it (ver 2000.80.2039.0) to c:\windows\system32\



After a re-boot no more connection problems.



Pls note that this is only for 2003 Web Edition with MSDE, this problem does not seem to occour with Standard Edition

mckoin at example dot com
18-Oct-2005 11:58


If you use PHP on Windows with Apache as a web server, you may get problems with authentication to MS SQL Server even when you supply all valid credentials. 



Check your php.ini file:

; Use NT authentication when connecting to the server

mssql.secure_connection = On



If you have secure_connection = On, make sure that you provide valid credentials in the properties for Apache service in the System Services box. Then you should not send DB username and password from your script to MSSQL Server.



If you want to use specific credentials from a PHP script, then set mssql.secure_connection = Off in your php.ini

j3d
24-Sep-2005 08:14


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.aspx?scid=kb;en-us;839569



<sarasm> Who would have thought that microsoft's article would be useful </sarcasm>

dale dot liszka at gmail dot com
20-Sep-2005 03:28


If you are trying to use Sybase and MSSQL on the same WIN32 box you will probably have headaches.  There are multiple declaration errors when you enable php_sybase_ct.dll and php_mssql.dll in the php.ini at the same time.



The specific error for all functions starts like this:

PHP Warning: Function registration failed - duplicate name - mssql_connect



There is a way around this! On a windows box I have done this successfully several times.



1. Make a backup copy of php_sybase_ct.dll (never hurts!)

2. Open php_sybase_ct.dll in a HEX editor (fhred is a nice free one http://www.kibria.de/frhed.html )

3. Replace all instances of 'mssql' with 'sysql'.  It doesn't have to be 'sysql', just anything but 'mssql' or 'sybase'.

4. Save the file.  (Alternately you could save as something like php_sybase_ct_modified.dll and cite that in your php.ini)



All the constructs are still declared for the sybase_* functions and now you can use MSSQL and Sybase instead of switching php.ini files, etc.  I have had this work under php 4.3+ and php 5.0.5.



PS. I decided to place this under mssql_connect because its the first error thrown in this case.  I believe it should make the information easier to find via google/search/browsing.

Marcel Laverdet
28-Jul-2005 04:30


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.

Robert at realjax dot com
14-Jun-2005 09:00


A view that worked fine when queried from any other environment was giving me strange results when queried from PHP.  The problem is that the MS SQL database settings are not set to the ANSI defaults as when connecting through Microsoft products.  The setting CONCAT_NULL_YIELDS_NULL defaults to ON when connecting with ODBC or SQL Query Analyzer, which complies with the ANSI standard.  However, this defaults to OFF when connecting through PHP.  There are many other settings which may also need to be explicitly set.

<?php

mssql_query('SET CONCAT_NULL_YIELDS_NULL ON', $hd);

?>

pelanne at gmail dot com
07-Jun-2005 10:18


Just in case it helps people here...  We were being run ragged by extremely slow connections from IIS6 --> SQL Server 2000.  Switching from CGI to ISAPI fixed it somewhat, but the initial connection still took along the lines of 10 seconds, and eventually the connections wouldn't work any more.



The solution was to add the database server IP address to the HOST file on the server, pointing it to the internal machine name.  Looks like some kind of DNS lookup was the culprit.  



Now connections and queries are flying, and the world is once again right.

jack dot rauta at tiscali dot dk
06-Jun-2005 07:30


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

RiderGian
30-May-2005 08:48


Yes!!!

It seams that the ntwdblib.dll shipped with PHP 5.0.3 is outdated. I've copied the ntwdblib.dll from the C:\winnt\system32 to the c:\php and everything start working.



By the way, the dll version i'm using is  2000.80.194.0



cheers



Gian

bass28
18-May-2005 03:47


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

ta_bg at NOSPAM dot mail dot ru
26-Apr-2005 04:48


I spent about a day until get working mssql_connect() function. I tried to connect PHP to MSDE. The solution is Server Network Utility (it is not installed on MSDE + Client tools configuration, only on SQL Server Standard/Enterprise). The necessary file SVRNETCN.EXE exists on the installation disk.

kryltsov at yahoo dot com
11-Apr-2005 04:33


If you connect to MSSQL and  get error 515 (INSERT NULL INTO NOT NULL VALUE). 

Execute following statement after connection to DB.

"set ANSI_NULL_DFLT_ON ON". There are lots of code arround where SP creates, for example, temp tables. There is no specification 'NOT NULL' on columns. Many people beleive that it means that nulls will be allowed.

After 5 days of debugging one of the software monsters SQL code I found that PHP connects to MSSQL (at least on my server) without setting "ANSI_NULL_DFLT_ON" which caused SP fired by insert trigger failed with 515 error because temp tables had been created with all columns 'NOT NULL'.

QA and from ASP everything was working just fine.



After I set ANSI_NULL_DFLT_ON to ON. Everything started working fine.

Hope it wil help.

yurtesen at ispro dot net dot tr
28-Mar-2005 11:21


If you are using FreeBSD you shouldnt forget to copy freetds.conf.dist to freetds.conf and edit. Otherwise you will get:

PHP Warning:  mssql_connect(): Unable to connect to server:  BLAH in

nospam_bg at pumkinhed dot com
10-Mar-2005 11:17


On freebsd I edited the Makefile in /usr/ports/lang/php5 and added  --with-mssql=/usr/local to the CONFIGURE_ARGS= section (ensure to put a \ in front of the previous line), then it will use tds.h correctly....  hth

senyahnoj
08-Feb-2005 06:09


When moving the following script from PHP on Win32 to PHP on Linux, I encountered problems:



$c = mssql_connect("SERVER\INSTANCE","UID","PWD");



After much searching, I discovered that 'instances' are just named aliases for port numbers, so on Linux this should be written:



$c = mssql_connect("SERVER:PORT","UID","PWD");



Please also note that the colon (:) should be used on Linux as the delimiter between servername and port number, not the comma (,) which only works on Win32 servers.

Ilmars Poikans
29-Jan-2005 03:08


Connecting to MS SQL server over TCP/IP.



I'm working on Windows XP with PHP5 (should work with PHP4 also because ntwdblib.dll is the same).



My problem was that I needed to connect to remote MS SQL server over TCP/IP (named pipes weren't an option), but I couldn't, because named pipes were used all the time.



One of the possible solutions is to install SQL Client Tools and configure TCP/IP support. I hadn't SQL Server installation disk, so I found fast solution with registry editing.



I added to registry following entry:



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\Client\ConnectTo] (strip space)

"DSQUERY"="DBNETLIB"



It worked! Just specify servername in form "host" or "host,port". I successfully connected to remote MSSQL7 and MSSQL2000.



I have found on Internet alternative solution that you need to add "DSQUERY"="DBMSSOCN", but it didn't work for me.



If you need more connection tweaking like 



[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\Client\DB-Lib] (strip space)

"AutoAnsiToOem"="ON" 

"UseIntlSettings"="ON" 



and more, then better find and install SQL Client Tools.

caleog at oeg dot vic dot edu dot au
24-Jan-2005 09:19


Named Pipes vs TCP/IP

i have a setup where the 'net server (using PHP) talks to a different db server (which has SQL Server). 

Named Pipes seems to be the faster of the two protocols.

The 'net server needs to use the Client Network Utility (usually c\windows\system32\cliconfg.exe) - it is the client in this case.

The SQL Server box in my case listens on both named pipes and TCP/IP, as defined in the Server Network Utility.

nickwsmith at lineone dot net
20-Jan-2005 10:07


The biggest problem i found was being able to connect to sql2k from one IIS box but not being able to connect using a different machine on the same network. (either iis 5 or6, win2k servers or 2003)



The trick is the NTWDBLIB.DLL (copied from  system32 on the sql box), make sure you user the same file on the IIS box as on the sql server you are trying to connect to.

Tobi
19-Jan-2005 01:57


The issue mentioned by PyRo is not solved by the replacement of the dll. Even with the 8.0 version, the same error occurs.

This fixed the problem in my environment:

- change the php.ini: "mssql.secure_connection = On" in the extension section for mssql.

- restart the IIS

- set up the mssql with the proper user settings (e.g. for iusr_servername) for the database.

petr /*dot*/ havlicek at phlab [dot) net
16-Jan-2005 08:17


I had problems connecting to multiple instance of MSSQL so I tried solution suggested by guilherme_cruz at uol dot com dot br 29-Aug-2002 07:45. It works great but I had to change line \\SERVER_NAME\pipe\MSSQL$INSTANCE_NAME\sql\query

 to \\.\pipe\MSSQL$INSTANCE_NAME\sql\query.

Regards

Petr

PyRo
14-Jan-2005 03:31


There seems to be an issue with the MSSQL extension and Windows Server 2003 Web Edition that prevents a connection to an SQL server, reporting an error like this "Not associated with a trusted SQL Server connection" when trying to use mssql_connect().



However, the bug seems to be residing in ntwdblib.dll, which comes with PHP and also with the Microsoft SQL Client.  I've found that using the SQL Client's version of ntwdblib.dll (version 8.0+) fixes the issues with MSSQL, but unfortunately the SQL Client cannot be installed on the Web Edition version of Server 2003.



So, the simple work-around or fix would be to obtain a newer version of ntwdblib.dll (8.0+) supplied from one of Microsoft's SQL applications and then overwrite your PHP version (7.0) with it on the Web Edition machine.  This should allow proper connectivity with mssql_connect().

henry aluede
24-Dec-2004 09:22


if u plan to connect to mssql, and u had previously worked on sybase, u will get a duplicate error. to correct this just exclude the sybase dll in your php.ini file

bartlewis at gmail dot com
16-Dec-2004 10:17


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.aspx?scid=kb;en-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.

cbunk at arescorporation dot com
14-Dec-2004 06:09


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.

chrisputnam at gmail dot com
18-Nov-2004 11:54


If you're having trouble getting PHP to connect to a SQL 2000 Server (the MSDE, specifically), try running the Server Network Client usually located at C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe



Disable all protocols but Named Pipes. Then Apply your changes, click OK, and restart the SQL service with these commands at a command prompt:



net stop mssqlserver [if it says the agent will stop too, let it happen]

net start mssqlserver

net start sqlserveragent



That fixed it all for me.



Also note that named pipes has a huge performance difference against TCP/IP. Named pipes are a tremendous amount faster.



I have been working on this for two days now and I hope someone else stumbles across my solution!

paul at ensigma dot com dot au
19-Oct-2004 08:10


It seems a lot of people are having trouble connecting to MSDE - so did I. Eventually I got it working. The trick was to change to registered server name from (local) to be the same as the machine name (ie in the Enterprise Manager, delete the registration and then re-register it). Also, make sure TCP/IP is enabled (and I actually removed named pipes for good measure). Viola - connection successful.

mworku at securiant dot com
03-Oct-2004 07:16


It looks like by default libphp5 ignores freetd's conf file if you 

specify your SQL server's ip address within the mssql_connect() function.



If you need to utilize freetds.conf file for connection parameters use the putenv function as follows



<?

  putenv('FREETDSCONF='path to your freetds conf. file')



  /* 

    SQL CODE

    mssql_connect('MSSQL_2000, 'user' , 'pwd'); 

    ....

    ....

  */

?>



All connection parameters should now be read from the conf file. Also don't enable logging unless you're debugging, performace will suffer.



/* freetds.conf

 [MSSQL_2000]

    host = 192.168.10.10

    port = 1433

    tds version = 8.0

;    dump file = /etc/freetds/log/freetds.log

;            dump file append = yes

;    debug level = 99

*/

vask at rocketmail dot com
03-Sep-2004 08:33


About the problem below: Most times this occurs when the windows guest account on the database server machine (i.e. the machine on which the database server is running) is disabled.

jonord at cfl dot se
11-May-2004 09:53


I had problems connecting to an SQL 2000 database running on a W2K server from IIS6 on an Windows 2003 server. I got an error message saying  "Unable to connect to server: <server name>" all the time. 



 The 2003 server didn't have the MDAC components installed, installing the latest version (2.8 as of writing) seems to solve that problem.

bianco at bluetone dot com
01-Apr-2004 09:12


Little idea about 255 characters limitation. :)



In tables I'm using column type "varchar" or "nvarchar" instead "text". And also I use for example varchar(8000). And there is "a bug". :) PHP will read this column only as varchar(255). So how to read more then 255 chars from varchar(8000)? It is very easy, use CAST to convert varchar type to text type.



select CAST(Comment as TEXT) from Job



PHP will read it. Check php.ini section [MSSQL] and set maximum text length. Check parameters



mssql.textlimit = 4096

mssql.textsize = 4096



Maximum length is 2147483647



Bye

nuying117 at 163 dot com
15-Mar-2004 11:35


I came across the same question as jack,but there was no effect when i use his method.and then ,I created a user named IUSER_YOUR_COMPUTER_NAME which is the same as the user who start the IIS process,it works!

markus dot amsler at oribi dot org
20-Feb-2004 11:23


Make sure you disable ANSI to OEM Conversion in your SQL Server Client Network Utility -> DB Library Options if you are using these extension from the CLI. Otherwise you get encoding problems with special characters. For further information look at http://bugs.php.net/bug.php?id=27324

stephan at towli dot ch
16-Feb-2004 11:12


Applies to : IIS 5.0, PHP 4.3.4, MS-SQL 2000



If you try to connect to a host with multiple SQL-Server instances, you have to copy the ntwdblib.dll from a MS SQL 2000 installation to the system32 directory of your webserver.



The ntwdblib.dll bundled with php 4.3.4 does not support multpiple server instance.



I used the DLL version 2000.80.194.0 that came with MS SQL Server 8.00.194.



Example:

$dbhost="server_name\instance_name,port_number";

$dbuser="foo";

$dbpass="foo";

$dbname="foo";

$db=mssql_connect($dbhost,$dbuser,$dbpass);

mssql_select_db($dbname,$db);

labrat457_minusthispart!_*-*_!yahoo.com
18-Jan-2004 03:35


If you aren't "married" to a linux distribution yet, and want a quick turn-key solution for connecting to a mssql server from apache/php running on linux...here's what worked for me.  Gentoo Linux has the freetds package (and sqsh, which is a cool tool) available as an emerge-able package (currently v0.61).  Install the latest Gentoo distro (at this time is 1.4).  Emerge freetds (and sqsh if you want it).  No special make flags were needed.  Download the latest tarballs of apache and php...follow the instructions for building statically.  Make sure you include the "--with-mssql" statement in php ./configure options.  With Gentoo, I didn't have to point the --with-mssql at a particular directory (I simply put "--with-mssql" and that's it) since freetds was installed via portage and was located automagically.  Freetds looks for a freetds.conf file in several places..one of them being /etc, which was where portage installed it.  My freetds.conf (minus comments and such) looks as follows:

[global]

        tds version = 4.2

        initial block size = 512

        swap broken dates = no

        swap broken money = no

        try server login = yes

        try domain login = no

        cross domain login = no

        text size = 64512

[MyServer70]

        host = 192.168.0.25

        port = 1433

        tds version = 7.0



This may not be the most optimized config, but it worked.  After everything is compiled and installed (with 4.3.4 I *did not* have to put in a line such as extension=mssql.so as earlier posts have mentioned in my php.ini file), start apache and try out your install with the following code snippet:



<?

$msconnect=mssql_connect("MyServer70","sa","");

$msdb=mssql_select_db("Northwind",$msconnect);

$msquery = "select titleofcourtesy,firstname,lastname from employees";

$msresults= mssql_query($msquery);

while ($row = mssql_fetch_array($msresults)) {

        echo "<li>" . $row['titleofcourtesy'] . " " . $row['firstname'] . " " . $row['lastname'] .  "</li>\n";

}

?>



Note, as previously mentioned, the "server" portion of the mssql_connect() must match the string used in the freetds.conf file.  My test mssql server was configured for "mixed mode authentication", "sa" for admin name, and blank password.  Adjust your connection string for your environment.



It may be just as easy to get this running with RedHat or other distro...your mileage may vary.  This, as I said, was what worked for me and it was very straightforward and worked without any special tricks.  Sorry if this has some redundant info in it, but I hope this helps someone :)  Cheers!

puppy2205 at yahoo dot com
22-Dec-2003 04:43


my problem with the mssql connection was that the password was too long...  i didn't test to see the max char allowable... but if your mssql password is long (more than maybe 10-15)...try shortening it to 6 just to test... this seemed to work for me... 



the only error was "supplied argument is not a valid MS SQL-Link resource"



hope this saves someone else 4 hours :)

jack at mvps dot removethis dot andthis dot org
10-Dec-2003 08:32


I'm running PHP 4.3.3 on IIS on a Windows XP workstation and connecting to SQL Server 2000 on a remote server.



I found that even though I had "SQL and Windows Authentication" selected, and even if I was using the "sa" account, I wasn't able to connect to the database and I would receive the message:



Warning: mssql_connect(): message: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (severity 14) in {filename} on line {line number}



I discovered that NT Authentication was turned on in php.ini:

[MSSQL]

mssql.secure_connection = On



By switching this to Off, the user name and password credentials supplied in the function call work correctly.

jeremyirons at genevus dot com
13-Jul-2003 08:51


Using MSSQL 2000 and Windows Server 2003 Enterprise running on a separate machine from the web server, I was unable to get PHP to connect to MSSQL with default Windows Server 2003 settings.  I got it working the following way:



From the Windows Server 2003 machine:

Control Panel -> Local Security Settings -> Security Settings -> Local Policies -> Security Options

Set "Network access: Let Everyone permissions apply to anonymous users" to Enabled



There is probably a better way, but this works

S.A. Tan
02-Jul-2003 06:58


Just a little comment that I would like to add:



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.

php at burntpopcorn dot net
04-Jun-2003 10:53


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());

Erik dot Uffelen-van*NOSPAM* at *rem*dsm dot com
15-Feb-2003 05:20


mssql_connect () ERROR due to invalid DLLs



I had a problem connecting to a MSSQL database, I want to connect to a compaq insight database.



Using: A workstation with: win 2K Server SP2

PHP ver 4.3 win32

MsSql 7.0 (on a remote system)

IIS 5.0 (comes with win2K)



What i did:

* get the php_mssql.dll

* get the ntwdblib.dll

* configure your php.ini



GET php_mssql.dll:

This dll can be found in the binary zip of php 4.3 (not the self installing package)

Put it in your extensions dir (extensions dir found in php.ini) In my case (default install with PHP installer) same dir as PHP.exe

NOTE: I dont know what the php_mssql70.dll library is.. never found it in a distribution of PHP or on the net.



GET ntwdblib.dll

This dll can also be found in the binary zip of php 4.3 in the dlls subdir. put it in the system32 dir.

WATCH OUT... i used a copy found on a MSSQL server, used that.. couldn't get it to work.. My function calls seamed OK, could call the mssql routines but I got a connection error 

When I used the dll distributed with the binary zip of php 4.3 it worked.



configure your PHP.ini

uncomment the php_mssql.dll in the extensions part of php.ini

also check if the extensions dir is set correctly.. it should point to the dir where your extensions are located.



Hope this helpes.!

Gordon McCague
13-Feb-2003 03:27


I am running PHP 4.2.1, Windows 2000, with a MSSQL Server 7.0 isolated from our web DMZ (separate DMZ for our SQL Server).



mssql_connect in version 4.2.1 seems to insist on using named pipes to connect to the SQL Server. This is not secure as it requires netbios browsing (tcp/udp ports 137, 139, and 445 (with windows2000)). This is an unacceptable security risk.



To resolve this issue use your MSSQL 7.0 CD to perform a  install on your web server. Select a custom install. You will be prompted to select your components. Uncheck the "Server Components" box to avoid installing the full server offering. Leave "Client Connectivity" checked. 



You can leave "Management Tools" selected as well if you wish to test your connection or run queries against the database engine. Installing them may pose a security risk so some might not choose to do so. 



Don't forget to install the latest service pack and MDAC components once the initial installtion is complete. The MDAC components can be obtained from http://www.microsoft.com/data/ 



SQL Service Packs can be obtained from http://www.microsoft.com/sql



Once you have installed the updates and patchs then you need to run the "Client Network Utility" to enable TCPIP as the network protocol for connectivity to your SQL Server. 



You will need to enable TCPIP and also create an alias to your SQL Server for TCPIP. This process should be self-evident once you run the utility. You may want to consult the SQL online manuals for assistance if uncomfortable with the configuration.



This should now allow you to access your SQL Server via ports 1433 and 1434 (or whatever you have enable as the port of communication). It is a best practice to consider changing the ports of communication to the SQL Server and I believe this can be done with the Network Client Utility (to something other than 1433 and 1434). 



You can always build a little test file to check your connectivity once your system is up and running.



Good luck!



P.S. I have not looked to see if PHP 4.3.0 resolves this issue but I don't plan to test too much. I like to know how the web servers are connected to the SQL server.

tsangel at hamosu dot yonsei dot ac dot kr
31-Jan-2003 05:03


To connect named instance, you have to specify the port number for name instance.

This document help you find the instance port number.



HOWTO: Connect to a SQL Server 2000 Named Instance with JDBC

( http://support.microsoft.com/default.aspx?scid=kb;en-us;Q313225 )



To find the SQL Server instance port number, follow these steps: ...blahblahblah...

Filipus
18-Jan-2003 11:02


Linux MDK 9.0, PHP 4.3.0 MSSQL 2000 on remote host

We used freetds (www.freetds.org) compiled with parameter:

./configure --prefix=/usr/local/freetds

PHP was compiled with parameter: 

./configure --with-mssql=/usr/local/freetds [and some others]



We added our section in /usr/local/freetds/freetds.conf

----------------------------------

[MyServer70]

   host = 172.16.20.81

   port = 1433

   tds version = 7.0

----------------------------------

in mssql_connect() you have to provide as a hostname the name you put between [] brackets in freetds.conf OR specify host:port



$connection = mssql_connect("MyServer70", "d", "d");

or

$connection = mssql_connect("172.16.20.81:1433", "d", "d");



Remember to specify port!

kokice_77 at yahoo dot com
10-Dec-2002 07:13


I'm running Windows XP, SQLServer 2000, Apache 1.3.23 and PHP 4.2.3. 



This worked perfecty for me:



<?php 



$myServer = "localhost"; 

$myUser = "sa"; 

$myPass = "password"; 

$myDB = "Northwind"; 



$s = @mssql_connect($myServer, $myUser, $myPass) 

or die("Couldn't connect to SQL Server on $myServer"); 



$d = @mssql_select_db($myDB, $s) 

or die("Couldn't open database $myDB"); 



$query = "SELECT TitleOfCourtesy+' '+FirstName+' '+LastName AS Employee "; 

$query .= "FROM Employees "; 

$query .= "WHERE Country='USA' AND Left(HomePhone, 5) = '(206)'"; 



$result = mssql_query($query); 

$numRows = mssql_num_rows($result); 



echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 



while($row = mssql_fetch_array($result)) 

{ 

echo "<li>" . $row["Employee"] . "</li>"; 

} 



?> 



Running example from this site:



http://www.devarticles.com/art/1/100/3



The thing is, you must choose authentication from windows AND SQL Server. Otherwhise it will not work. During the installation of SQLServer you choose this option, and set a password to the user "sa".



Hope it helps.

cdthompso1 at yahoo dot com
20-Nov-2002 02:36


My config:



 webserver - PHP 4.2.3 (from php.net) + W2K SP2 + IIS5 

 database server - MSSQL2K



My webserver never had the SQL2K client installed so I copied NTWDBLIB.DLL into c:\winnt\system32, as the instructions stated.  Using the NTWDBLIB.DLL that came packaged with PHP, I could not connect to my database server.  I then copied the version of NTWDBLIB.DLL from my database server to c:\winnt\system32 on the webserver and was immediately able to connect successfully.  I assume this is because I am using MSSQL2K and the NTWDBLIB.DLL packaged with PHP is for MSSQL 7.



NOTE:  unlike the user above, when I look at phpinfo() output, the mssql version is still reported as 7.0, though I am using NTWDBLIB.DLL from a MSSQL2K install (8.0?).  Perhaps this is compiled into the php_mssql.dll and does not come from NTWDBLIB.DLL.

sstamps at nexvs dot net
14-Nov-2002 05:25


There is a copy/paste error in the above text where it refers to the server name having to appear in the 'interfaces' file. This may have been true at one time, because somehow Sybase and MSSQL support used a similar interface, thus this was probably copy/pasted into this section from the Sybase version of this function. However, it is no longer true.



I was having a problem getting MSSQL to work and was having a devil of a time finding ANY documentation on the 'interfaces' file for MSSQL (mainly because it DOES NOT EXIST); turns out it was a config problem that was easily fixed, once I stopped wasting time chasing the wild goose.



SO, the short version is to IGNORE the reference to the 'interfaces' file above for MSSQL, and look elsewhere for problems getting MSSQL support working. Just a friendly bit of time-saving advice. :O)



Also, the format of the 'servername' parameter is: 'hostname_or_ipaddress[,port]' . Ex: 'dbs.mydomain.com' or '10.45.234.17,2031'. Only append the port number if your MSSQL server uses a port other than the default port 1033. Using a colon instead of a comma doesn't work (at least not for me, but YMMV).

damien at mightyoaks dot com
13-Nov-2002 08:03


SQL Server 2000 may be listening on port 2433, instead of the default port 1433 that the MSSQL client libraries and old MSSQL servers use.  In this case, you might end up with mssql_connect() using port 1433 and SQL Server denying the connection.



If you are unable to connect, you can start by verifying the port SQL Server is listening on.  From the database server, run Start->Program Files->Microsoft SQL Server->Server Network Utility.  Select TCP/IP from the list of protocols and press Properties... - it will say what port the server is listening on (usually 1433 or 2433).



With SQL Server 2000 SP2 I was unable to change the default TCP/IP port on the server side.  But you can still change the default port the client libraries are using.  On your PHP machine, first make sure you have the MS SQL server Client utilities installed, then run Start->Program Files->Microsoft SQL Server->Client Network Utility.  Choose "TCP/IP" from the Enabled protocols list and press "Properties...".  Enter the port number that the server is listening on here (2433 in my case).



Once server and client are using the same port, PHP will be able to connect properly.

s dot westermayer at nospam-netnnet dot de
04-Sep-2002 08:34


SQL Server Authentication Mode must be set to "SQL Server and Windows" or your SQL Server will not accept a connection and diplay: 

Warning: MS SQL message: Login failed for user 'myuser'. Reason: Not associated with a trusted SQL Server connection. (severity 14) in...

guilherme_cruz at uol dot com dot br
29-Aug-2002 01:45


>> Problems With Multiple Instances <<



If you are having problems to connect PHP 4.x to MSSQL 2000 on Win2K, there is a simple way to solve it:



Open the Client Network Utility and do the following:



1. Add a new Alias choosing Named Pipes

2. Type in the Alias field (anything you want): CONN1

3. Erase Server field and type exactly: SERVER_NAME\INSTANCE_NAME



The field Pipe Name should be: \\SERVER_NAME\pipe\MSSQL$INSTANCE_NAME\sql\query



Now, in PHP, use:



mssql_connect('CONN1', 'username', 'password');

or

mssql_pconnect('CONN1', 'username', 'password');



Regards

Guilherme Cruz

jackveral at yahoo dot com
23-May-2002 01:32


PHP 4.X + Win2000Server + IIS5 + SQLServer 2000



Problems establishing the conection.



Besides my comment about the instances of SQLServer (see above), its very important to have the latest version of NTWDBLIB.DLL. With Php 4.2.1 comes de NTWDBLIB version 7.00, with this version the conection isnt work! you have to install the version that comes with the client of SQLServer 2000. Be careful, because installing the client sometimes doesnt replace the NTWDBLIB, then search for it in the SQLServer Installer Disc an replace it in the system32 directory of Windows(SQLSERver 2000 comes with Version 8.00 of NTWDBLIB.DLL).



Bye!



;-)

ianm at firstlinux dot net
17-May-2002 05:14


I was unable to connect to the SQL Server with mssql_connect($host,$user,$pw) - despite having checked and re-checked user names and passwords - until I changed the user authentication type from NT Authentication to SQL Server Authentication.

jackveral at yahoo dot com
25-Apr-2002 08:12


Problems using mssql_connect with MSSQL 2000. 



You have to:



1 - have installed MSSQL 2000 Client on the server that run PHP. This allows to use the libraries of MSSQL.



2 - when you use mssql_connect the parameter SERVERNAME has to be SERVERNAME\INSTANCE. 

   Ej: mssql_connect("MYSERVER\\MYINSTANCE","USER","PASWWORD")



NOTE the use of \\ intead of \, because this is escape caracter. This is very important!!



If you dont have configured instances on your server use as instance the name of the server.

   Ej: mssql_connect("MYSERVER\\MYSERVER","USER","PASWWORD")

fbellavance at agricom dot ca
10-Apr-2002 06:41


I managed to solve the probleme above. Here is how I did it. Hope this helps someone else. 



first, I created a new instance of MSSQL 2k using the installer. There are 2 things I did that are important. first, I didn't use the default port (by default, if you install a new instance, it actualy uses the same port as the default one, stupid I know, but that's how it seams to work). Then I also removed, in the name pipe name, the $instancename. this is important later. 



now, for some reason, mssql_pconnect wouldn't take the port number we specified to it. Even using the default instance/port it wouldn't connect (1433). So what I did was create an ODBC entry that had the name I wanted to use to connect to my db, and omited EVERYWHERE to mention the instance's name. Then, I connect using the server name and the port I specified for the new instance in client configuration, 

in the ODBC link. Then when I use pconnect, I use it like this mssql_pconnect("$odbc_link_name","$username","$password");



It seams like it uses the ODBC link like a DNS entry to find where to connect to the database server. Hope this helps :)

fbellavance at agricom dot ca
09-Apr-2002 01:48


Note that in MSSQL 2000, there is a new fonctionality called multiple instances. They are accessed using <servername>\<instance name>

However, this does NOT work under php. You may only access the primary instance. 



The reason for this seams to be that the library doesn't support the format. Instead, it seams to make a call to a server called <servername\instancename>. This results in a failed connection. 



makes you wonder why M$ didn't just make it so you could access a second mssql deamon on a different port.

michael at gillam dot com
04-Apr-2002 01:57


PROBLEM: 

php_mssql.dll Extension Not Found on Windows 2000 ?



Getting a 'Unable to Load Dynamic Library' error for php_mssql.dll despite the extension being *exactly* where it should be?



ANSWER: The answer is you need the *latest version* of php_mssql.dll.  PREVIOUS VERSIONS OF THE DLL DO NOT WORK! 



Download the latest Window binary from http://www.php.net/downloads.php 

(The 5.8MB+ version.) Unzip it and look for the extension you need.

mazdak at actfit dot com
25-Jun-2001 12:19


Procedure for M$SQL Support in PHP 4.0.5+/*NIX


==============================================





What We Need ?


==============


* PHP Source (latest)


* FreeTDS (http://www.freetds.org/) get latest stable version


* Patience!!!


* Dual Login Mode in SQL Server Enterprise Manager 


 (Windows ONLY login will not work!)








Installation Procedure !


========================


(1)


> tar -xzvf freetds-xxx.tgz


> cd freetds-xxx/


> ./configure --prefix=/usr/local/ftds --with-tdsver=4.2 --enable-dbmfix


> make && make install





(2)


> Edit your /etc/ld.so.conf (or equivalant) add /usr/local/ftds/lib to it


> run ldconfig (located in /sbin/ldconfig in linux)





(3) GET READY TO HACK!!!


> tar -xzvf php-xxx.tar.gz


> cd php-xxx/


> edit 'ext/sybase/config.m4', replace 'dbopen' with 'tdsdbopen'


  (in version 4.0.6 of PHP this in on line 27 of config.m4)


> (I am not sure if this step is 100% necessary, but do it anyway!)


  edit the 'configure' file and look for PHP_SYBASE_DBOPEN, you should see two 


  different versions of this.  As follows:


  #define PHP_SYBASE_DBOPEN tdsdbopen    <-- leave this alone


  #define PHP_SYBASE_DBOPEN dbopen       <-- change this to #define PHP_SYBASE_DBOPEN tdsdbopen





(4) 


> ./configure [put other php options here] --with-mssql=/usr/local/ftds --with-sybase=/usr/local/ftds


> make && make install


> cp php.ini-dist /usr/local/lib/php.ini  (the location of this file may differ for your installation)





(5)


> Now, edit /usr/local/lib/php.ini file, look for 'sybase.interface_file' directive


> uncomment it and change the value to /usr/local/ftds/interfaces (location where you installed freeTDS)





(6)


> Edit /usr/local/ftds/interfaces file, for MSSQL, you have to add


  an entry like the following*:


  


  mssqlconnection


      query tcp tds4.2 IPADDRESS_OF_SQL_SERVER 1433


      


  *  when writing PHP code, use the name "mssqlconnection" for hostname,


     so you would use something like:


     $conn = "mssqlconnection";


     $username = "myuser";


     $password = "mypass";


     $dbc = mssql_connect ("$conn", "$username", "$password");


     


(7)


> Do a check to see if everything is OK, by starting Apache.

yeppikae at hotmail dot com
29-Mar-2001 08:02


If you're doing all the above stuff on home computer, then chances are that $hostname="localhost" might not work. I have seen this happen few cases, including mine. Well, i was messing around with my home lan. Anyways make sure that you point $hostname to your machine name. That is if your machine name is say "MYSELF" then have $hostname="MYSELF". It does the trick! 





Reason for this - well as far as i understood if you assign a static private IP address to your NIC (i.e. you're not using DSL or cable, or better not connected to net) then the loopback doesn't point to your IP address but to 127.0.01. And localhost points to 127.0.01. Well the apparent mix up.

add a note

mssql_connect

Description