PHP : Function Reference : Microsoft SQL Server Functions : mssql_connect PHP Examples Tutorials References

Examples ( Source code ) » mssql_connect


<?php

// Server in the this format: <computer>\<instance name> or 

// <server>,<port> when using a non default port number

$server = 'MYPC\SQLEXPRESS';


$link = mssql_connect($server, 'sa', 'phpPassword');


if(!$link)

{

    die('Something went wrong while connecting to MSSQL');

}

?>

Code Examples / Notes » mssql_connect

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

When trying Linux + PHP 5.2.0 + SQL express 2005, use dinamic port specified in TCP/IP properties of SQL server configuration. The default port (1433) didn't work for me. This is my freetds.conf file: [JRZMERC176] host = 10.12.51.176 port = 1396 tds version = 8.0

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.

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

well after searching for a solution to the problem of communicating with remote SQL Server DB's through PHP, I came across this little tidbit. Just put this in your registry. I donâ€™t know what it is, I donâ€™t know why it works, I really don't care enough to find out. BUT IT DOES WORK! [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\Client\ConnectTo] "DSQUERY"="DBNETLIB" I hope this will save some people from the nightmare that seems to be associated with MSSQL Server and the PHP MSSQL_CONNECT();

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

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.

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

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

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

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.

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.

The biggest problem is that the fucking "ntwdblib.dll" delivered with php for windows does not work with mssql-2005. If u use a some years old one from mssql-2000 it connects with ".\SQLEXPRESS" On a network-address/name it does not work, the php from the host-sytem (fedora) can access to "localhost:1433" but the vm himself does not :-)

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.

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

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.

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.

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.

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

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.

None of the above solutions worked on Vista box/IIS 7/php5.2/MSSQL express 2005 with mssql_connect.

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"); ?>

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.

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.

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

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

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!

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.

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.

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.

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 */

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!

If you're getting a timeout when trying to connect to a remote server, the registry hack seems to fix it. Another funny way to do this is: 1. Create a DSN to the server you'd like to use, typing in the server's IP or name in the "Server" drop-down. 2. On the second screen, click "Client Configuration" and change something (anything, but I unchecked "Dynamically Determine Port"). 3. Go through the remaining screens as normal. 4. The "ConnectTo" registry entry will be created. Note that it will NOT be created if you fail to click "Client Configuration" (ref: http://support.microsoft.com/kb/229929) 5. Edit as stated: "DBNETLIB,$Server[,$Port]" 6. iisreset and your mssql_connect() should now work. You can also actually delete the DSN you created if you don't need it, since the registry key is not tied to the specific DSN but instead the list of servers in the DSN wizard. Also, if you're getting authentication errors (I was on a different server), check to make sure that mssql.secure_connection is set to 0 or commented out. A 1 setting will cause the web server to use its Windows authentication (IUSR_) to log in to the SQL Server.

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

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.

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. Also, the webzilla site for the updated ntwdblib.dll file seems to be having connection issues with their MySQL database. Instead, try http://www.userscape.com/helpdesk/index.php?pg=kb.page&id=13 . I hope that this saves someone a few hours :-) .

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!

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

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.

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

I'm using WAMP5 1.6.6 with XP Media Center 2005 and I had to update ntwdblib.dll in 2 places to get mssql_connect to access a remote sql server. I updated ntwdblib (version 2000.80.194.0) in the \wamp\php and the \wamp\apache2\bin directories. Note that I'm running apache, not IIS.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

I had lots of issues with trying to get SQL Server '05 (NOT Express) to work with PHP 5.1.2 on Windows Server '03 SP2 running IIS 6. This is what finally worked: - Uncomment the php_mssql.dll line in php.ini - Replace the ntwdblib.dll file in /php/ext/ with the (smaller) downloadable file from webzila.com - Set SQL Server to mix-mode authentication ("Windows and SQL Server Auth") - Create IUSR_<computer_name> account in SQL Server using Windows authentication - Grant read/write access to the IUSR_<computer_name> in SQL Server - Create SQL auth account using SQL authentication - Grant read/write access to the SQL auth account in SQL Server - Restart the system I'd imagine that creating a way for IUSR to get into the SQL Server is NOT a good idea, but this is the only way I could get it to work. Hopefully, a future update to SQL Server will fix this.

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.

I encountered this while developing applications for Cisco IP Phones. The phones require the Connection header to be set to close: header("Connection: close"); But, with this header, mssql_connect won't work. A possible workaround for this is to have another php file that interacts with the database, and returns the results in xml format so you can use it in the application. Hope this saves someones time. streamkid

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

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

I connected a WinXp prof ver 2002 sp2 box to a MsSQL server 2005. Here is how (Assuming the MsSQL server is already running). I installed the IIS via WinXP cd. I downloaded and ran the file: php-5.2.1-win32-installer.msi (I chose IIS + ISAPI in the installation process) I downloaded the files: ntwdblib.dll (as mentioned in various posts above) and php_mssql.dll (found it in other PHP distributions) I copied those files into the root PHP directory and modified the PHP.ini adding: extension=php_mssql.php. (note: The downloaded file php_mssql.dll may have to be put in the php\ext folder instead of the root php folder depending on your extension path) Reboot the IIS. The php code for the connection is: $this->sql_link = '0'; function connection() { $this->sql_link = mssql_connect($this->"MyHost-NotIP", this->"MySelf", $this->"SomePass"); mssql_select_db($this->"Databasename", $this->sql_link); }

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!

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.

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.

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)

Hi, just trying to save some time for those using windows Vista + WAMP 5.1.7 (Apache as the HTTP server, not IIS 7) I was having trouble connecting to SQL Server 2005 Express and I hope the following procedure helps someone out there. 0. Stop all services from WAMP 1. get ntwdblib.dll version 2000.80.194.0 and copy it to the following locations: a) wamp\php\ b) wamp\Apache2\bin\ c) windows\system32\ 2. Configure SQL Server to accept TCP connections and Named Pipes as discussed in earlier posts. (no need to change port settings) 3. Configure SQL Server for Mixed mode authentication and remember the password you set for sa 4. Restart SQL Server Service 5. Open wamp\php\php.ini and set the mssql.secure_connection = On 6. 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 7. Once you added 'Everyone' make sure you check the Full Control checkbox in the permissions list 8. Click on start all services from the WAMP menu to connect, do something like the following: $cnMsSQL = mssql_connect('.\SQLEXPRESS', 'sa', 'yourpassword') or trigger_error(mssql_get_last_message(),E_USER_ERROR); Well, that's it... that's how I made it work. Hope that this solves someone's issue

Hi all, jcastromail at yahoo dot es Steve H lbowerh at netscape dot net munckfish.net are all talking about SQL Server 2005 Express Edition. I had a problem with it too. It was a pain to make it working. If you run into a trouble, follow their advices and maybe these three: try port 1221 play with the SQL Server Configuration Manager play with the SQL Server Surface Area Configuration there are many options which may affect this working or not. All you ge is a general message: Unable to connect: SQL Server is unavailable or does not exist. Unable to connect: SQL Server does not exist or network access denied. (severity 9) I finaly maked it work with: $host = 'MACHINENAME'; // see? without \SQLEXPRESS $port = 1221; php.ini: mssql.secure_connection = On ntwdblib.dll: version = 2000.80.194.0 after playing with the configuration tools (enable TCP, and some...). I can't say if playing with cliconfg has any effect. HTH, Robert.

Hello, i had problems with umlauts in an mssql database and php 4/5 on a windows 2000 server. On windows 2003 there were no problems. to solve the problem i added // convert charset iconv('cp852', 'latin1', $return_value); to my code. Perhaps its usefull for someone. Regards Jan Gehring

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

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.

For those poor people (like myself) who have to use MS SH*T SQL 2005 and cannot connect it. 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. DO NOT for get restart you sql server service and pray to dear God your company will dump MS one day. DO NOT install "ActiveScript engine" as MS suggested. You don't want extra trouble and junk.

For the life of me I can't seem to get this. I have numerous databases on various site, a lot of them the same. What I woud like to do is to make my db work on 2 different site. site A is where it resides and Site B is where I want to read it On Site B I would normally use a 'localhost' 'usernme' 'password' type thing but now sice the db is on a different host I can't seem to get it to read. I tried replacing localhost with a direct address http://www.siteA.com and that didn't work. Am I miossing something and is this possble?? Phil

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

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)

Finally, I made it work... Using Windows 2003, ISS 6.0, PHP5-CGI and SQL express It's a good idea to set your environment variables to path=%path%;c:\php and phprc=c:\php first. 1. Fire up SQL Server Configuration Manager 2. Drill down to "Protocols for SQLEXPRESS" 3. Enable Named Pipes (God knows why) 4. Enable TCP, I have everything enabled in the "IP Addresses" tab 5. Restart SQL Express (Or the computer ;) 6. Start SQL Browser service. Remember to set it to automatic. The final detail is the need to use (host)\SQLEXPRESS in the connection string. This is a little problematic when using MDB2, but it works if you break the connection string into an array like this: $dsn = array( 'phptype' => 'mssql', 'username' => 'user', 'password' => 'yourpass', 'hostspec' => 'localhost\SQLEXPRESS', 'database' => 'yourdb', ); require_once 'MDB2.php'; $mdb2 =& MDB2::connect($dsn); if (PEAR::isError($mdb2)) { echo $mdb2->getUserinfo(); } Of course, youâ€™ll also need extension=php_mssql.dll in the PHP.ini and that ntwdblib.dll 2000.80.194.0 file mentioned bellow. Have fun

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.

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.

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

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.

After wasting mine and a few others' time on the PHP/freeTDS -> MSDE/MSSQL connection issue, I found this note in the main MSSQL documentation here in the php docs: """ Add the line: putenv('TDSVER=70'); just before calling mssql_connect() """ That's the thing that fixed it in my case, nothing else was necessary. good luck!

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>

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.

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

A small problem I had when connecting to SQL 2000 Ent named instance (from W2003 Std, IIS6, PHP5). I had to supply the instance name AND port $sql = mssql_connect ("SERVER\INSTANCE,PORT", "username", "password") And also add registry entry as per previous comments: HKEY_LOCAL_MACHINE \SOFTWARE\ Microsoft\ MSSQLServer\ Client\ ConnectTo: "SERVER\INSTANCE"="DBMSSOCN,SERVER\INSTANCE,PORT" (Just export the key from a PC with SQL client tools)

1. Installed the SQLServer in mixed auth mode 2. Checked that the mssql.secure_connection = Off was present in the php.ini 3. Copied the ntwdblib.dll file into o WINDOWS\SYSTEM32 o program files\apache2\bin o php 4. Started the SQL Server Browser Service 5. Enabled all of the IPs in Protocols > TCP/IP 6. Restarted the following services: o Apache o SQL Service o SQL Browser Service This allowed me to connect with no problems using the following parameters within my mssql_connect call HOST=.\SQLEXPRESS USERNAME=sa PASSWORD=<MYPASS>

mssql_connect

Open MS SQL server connection (PHP 4, PHP 5, PECL odbtp:1.1.1-1.1.4)
resource mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] )

Code Examples / Notes » mssql_connect

Change Language

mssql_connect

Open MS SQL server connection (PHP 4, PHP 5, PECL odbtp:1.1.1-1.1.4) resource mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] )

Code Examples / Notes » mssql_connect

Change Language

Open MS SQL server connection (PHP 4, PHP 5, PECL odbtp:1.1.1-1.1.4)
resource mssql_connect ( [string servername [, string username [, string password [, bool new_link]]]] )