Last updated: Sun, 25 Nov 2007

mssql_connect

(PHP 4, PHP 5, PECL odbtp:1.1.1-1.1.4)

mssql_connect — Open MS SQL server connection

설명

resource mssql_connect ([ string $servername [, string $username [, string $password [, bool $new_link ]]]] )

mssql_connect() establishes a connection to a MS SQL server. The servername argument has to be a valid servername that is defined in the 'interfaces' file.

The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mssql_close().

매개변수

servername: The MS SQL server. It can also include a port number. e.g. hostname,port.
username: The username.
password: The password.
new_link: If a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. This parameter modifies this behavior and makes mssql_connect() always open a new link, even if mssql_connect() was called before with the same parameters.

반환값

Returns a MS SQL link identifier on success, or FALSE on error.

변경 기록

버전	설명
4.4.1 and 5.1.0	The `new_link` parameter was added

참고

mssql_data_seek

mssql_close

Last updated: Sun, 25 Nov 2007

add a note User Contributed Notes
mssql_connect

robert dot johnson at icap dot com
04-Oct-2008 07:39


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



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



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



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

alfredok at google server dot com
03-Oct-2008 10:43


Hi everybody,



 I was exactly 10 hours to make mssql work with Microsoft SQL Server 2005 Express 9.0.3.042, PHP 5.2.6, Apache 2.2.8 and Windows XP all working on the same server. PHP and Apache from WampServer 2.0c [05/05/2008].



 I tryed a lot of things, all you see here, but the last one (it might have been the first if I started from there) was copying ntwdblib.dll version 2000.80.194.0 to the following locations:



a) wamp\php\

b) wamp\Apache2\bin\

c) windows\system32\



 It haven't worked until I copied it on wamp\Apache2\bin\, that was the last step (I tested each).



 So, THIS IS VERY IMPORTANT, start from here, then try the rest to solve other problems.



 I want to thank all the people that wrote here for your unvaluable help.

rcgandara at gmail dot com
17-Sep-2008 10:27


If your are trying to connect to a MSSQLServer 8 and got this error: Unabled to connect to: [SERVER_NAME] 

and u r using Server authentication, make sure 2 set up mssql.secure_connection to Off in da php.ini



RCGP 



Making this world better everyday... peace out

Anonymous
11-Sep-2008 01:18


This might be obvious to some, but here is a quick tidbit that might save you some time if you're using FreeTDS in Linux:



Be sure that you have these two lines in freetds.conf:

dump file = /tmp/freetds.log

dump file append = yes



so you can tail -f it in the background of debugging the problem.  This helped me find my issue on on CentOS Linux:  

 

1) tsql test works

2) php-mssql connection in php also works WHEN RUN FROM THE SHELL

3) running PHP through apache does NOT work.



my /tmp/freetds.log file told me: 

net.c:168:Connecting to MYDBSERVER port MYDBPORT

net.c:237:tds_open_socket: MYDBSERVER:MYDBPORT: Permission denied



and the answer was my firewall/SELinux was denying the Apache processes access to connect to the remote MSSQL DB port, but my shell accounts were fine.

matias dot perroneNO at globalinnovation dot com dot ar
06-Aug-2008 02:08


I have a Apache 2.2 with PHP 5.2.



I followed the steps at the previous notes, but the only thing that solve the problem "Unable to connect to server" was replace the "ntwdblib.dll" located at the root of php directory, (usually c:\php\ntwdblib.dll) with the new version of ntwdblib.dll downloaded from Webzila (with one L).



You also can download the same file at:

http://www.globalinnovation.com.ar/downloads/ntwdblib.rar



Uncompress and replace the file, c:\[PHP ROOT DIR]\ntwdblib.dll



Regards.

annon201 at gmail dot com
23-Jul-2008 09:15


Yes! Got PHP to connect to MSSQL with NT Authentication from a WinXP box.. Followed what a previous user said:



Set the Apache service to run as domain\username in the Windows Services Manager.



Set mssql.secure_connection to On in php.ini



and connect WITHOUT passing any credentals... 

$sql = mssql_connect("server"); 

mssql_select_db("database");

getzeroedin at hotmail dot com
08-Jul-2008 11:41


For those trying to connect PHP 5.2 to a 2005 Microsoft SQL Server Analysis Services (SSAS) cube and execute MDX queries, you have to establish a link via a trusted connection from the Database Service to SSAS using a stored procedure, then execute the stored procedure via PHP. 



Here is an example stored procedure that retrieves records from the Adventure Works sample cube that ships with SSAS.   



From SQL Server Query Analyzer, you could test it as:  



exec testMDX 



From PHP, you would execute something like the following:



$resultset = mssql_query("exec testMDX",$res_id);



then loop thorugh the result set.



-- STORED PROCEDURE BEGIN



set ANSI_NULLS ON -- Must be enabled at time Stored Proc is created

set QUOTED_IDENTIFIER ON -- Must be enabled at time Stored Proc is created

GO



Create Procedure [dbo].[testMDX] 

as

BEGIN

SET ANSI_WARNINGS ON:  -- Must be enabled

SET ANSI_NULLS ON; -- Must be enabled



Declare 

@SQL varchar(1200),  -- Variable to hold SQL query

@MDX varchar (1000), -- Variable to hold MDX query

;



-- Establish a link to Analysis Server

exec sp_addlinkedserver 

@server='linked_olap', -- Alias used to reference the link

@srvproduct='', -- Not used

@provider='MSOLAP.3', -- OLAP driver

@datasrc='servername', -- Database server name

@catalog='Adventure Works DW Standard Edition' -- Database name

;



-- Analysis Server requires a TRUSTED connection

exec sp_addlinkedsrvlogin 

@rmtsrvname = 'linked_olap', -- Alias used to reference the link

@useself = 'false', -- Use own credentials

@locallogin = NULL, -- Apply to all local logins

@rmtuser = 'domain\username', -- Remote user name

@rmtpassword = 'xyz123' -- Remote user password

;



-- Create a temporary table that will be used to hold the MDX output

create table #temp_table (column1 text null, column2 text null);



-- Setup a string to hold the MDX so that the precompiler does not try to validate the syntax

SET @MDX = 'SELECT [Product].[Category].members ON ROWS,

                    {Measures.[Internet Order Count]} ON COLUMNS   

                    FROM [Adventure Works]    ' ;



-- Setup a string to insert the MDX results into the temporary table

SET @SQL = 'Insert into #temp_table SELECT * FROM OpenQuery(linked_olap,'''+@MDX+''')';



-- Execute the SQL and remote MDX query

EXEC (@SQL) ;



-- Select the results from the temporary table to return to the calling program

Select column1, column2 from #temp_table ;



-- Drop the temporary table

drop table #temp_table;



-- Release the TRUSTED connection 

exec sp_droplinkedsrvlogin 'linked_olap', NULL ;



-- Release the link to the Analysis Server

exec sp_dropserver 'linked_olap' ;



END



-- STORED PROCEDURE END

cshatto at pachamber dot org
30-Jun-2008 10:27


On IIS running as ISAPI, if you're changing the DLL from the old copy in PHP to the newer copy that works with SQL 2005, don't forget to restart the IIS service. I messed with the newer dll for an hour before it dawned on me I'd forgotten to restart the service to load up the new dll file into memory.

Anonymous
15-May-2008 09:29


After finishing my previous post (http://nl2.php.net/manual/en/function.mssql-connect.php#83024) about using FreeTDS throug php_mssql.dll and thinking all my problems were solved i ran into a specific php_dblib.dll problem which is described here



http://forums.devnetwork.net/viewtopic.php?f=2&t=78768



I finally cured my problem using Microsofts own PHP driver (which is not supported here i think but has as fairly good help file and can be used together with php_dblib.dll or php_mssql.dll) 



http://www.microsoft.com/sql/technologies/php/default.mspx



Could be a life saver. :)

cajunzerangue
14-May-2008 01:57


Current setup IIS 5.1 (Windows XP) / PHP 5.26 (including PECL 5.26 extensions) / MS SQL Server 2000



The following code worked flawlessly for me. (Borrowed from http://www.webcheatsheet.com/PHP/connect_mssql_database.php)



<?php

$myServer = "ServerName\ServerInstance";

$myUser = "dbuser";

$myPass = "dbpassword";

$myDB = "DBName";



// connection to the database

$dbhandle = mssql_connect($myServer, $myUser, $myPass) 

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



// select a database to work with

$selected = mssql_select_db($myDB, $dbhandle)

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



echo "You are connected to the " . $myDB . " database on the " . $myServer . ".";



// close the connection

mssql_close($dbhandle);

?>



ALSO, it's important to note that I did have to replace the ntwdblib.dll that was included with my PHP 5.2.6 source, and replace it with the latest version which you can find on   http://www.webzila.com/?wz=dll.

dutchben at hotmail dot com
07-May-2008 06:45


One other option to connect to MSSQL Express without mucking about with the ntwdblibb.dll. Use the FreeTDS extension as explained for use in Moodle here



http://docs.moodle.org/en/Installing_MSSQL_for_PHP



Solved my problem and saved and seems to work much better than the standard php_mssql.dll extension.

db in mn
17-Apr-2008 02:08


see post "post at wmm dot no" in the mssql page.  installing new mdacs on windows 2000 server fixed my problem after trying everything possible posted on this page.

Anonymous
14-Mar-2008 06:11


To make the FreeTDS work with PHP 5 (both self compiled) I needed to set some enviroments variables:



For a bash shell, this looks as follows:

> SYBASE=/usr/local/freetds

> LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib

> export SYBASE LD_LIBRARY_PATH 



More info: http://www.peceny.de/misc/freetds.html 



Don't ask me why, but it works afterwards.

simonhukin at gmail dot com
13-Mar-2008 04:15


After a couple of days (seems to be the common theme) trying to get my code to connect to our new clustered 2005 SQL server instance. I finally did it. :-)



For the following setup:



PHP 5.2.4 (hosted on a Windows Server 2003 box)

connecting to MSSQL 2005 in a clustered environment

(was already connecting to MSSQL 2000 with no problems)



I was about to get the client tools installed on the server, but this would have required a trip down to the data center by our IT guys (last resort - well actually last resort was to write a data access service in Java, which was connecting fine).



Anyway, after making sure all the ntwdblib.dll were of the 2000.80.194.0 version. It was still not working.



The thing which made it work was simply to specify the port.



yes after 2days of looking into alternatives (incidently freetds - a windows compiled version, was working very intermittently) I found all I had to do was specify the port!



I guess this is because I needed to connect via TCP/IP, our network admin didn't want named pipes used (as it's non-standard). Although, named pipes were enabled anyway. Specifying the port must have made the default behavour to connect via TCP/IP. Also the port was '1433' which I assumed was default anyway, hence hadn't specified before.

Slartibartfast
21-Feb-2008 09:25


Using Apache on a windows machine to connect to a networked SQL Server I was getting the following error:



Warning: mssql_connect() [function.mssql-connect]: message: Login failed for user 'DomainName\MachineName$'. (severity 14)



This occurred regardless of the username and the password that I passed to mssql_connect()



Apparently the username and password parameters are ignored when connecting to SQL Server with trusted connections. Instead it attempts to connect using the  Web Server username and password instead. Under windows the default webserver account is the machine name (which is probably not set up as being a windows user).



This can be solved by configuring Apache to log on as a designated user.

-    Open Control Panels

-    Double click administrative tools

-    Double click services

-    Right click on Apache and choose properties

-    Under the "Log On" tab choose "This Account" and type in a valid domain user account & password

-    Restart the Apache service

edortizq at gmail dot com
03-Dec-2007 07:41


For connecting Linux + Apache + SQLEXPRESS 2005 take care about it:



- Don´t use the standard MS-SQL port (1433), use the MS-SQL dinamic port under SQL Server Configuration Manager -> SQL Express Protocols -> TCP/IP properties -> IP Adresses -> IPAll



- You can do a direct connection (without FreeTDS) using the following statemt:

  $db=mssql_connect('192.168.xxx.xxx:1541','usrxxxx','pwdxxxx');



-You can use FreeTDS configuring the freetds.conf file as follow:

  [connect2k5]

  host = 192.168.xxx.xxx

  port = 1541

  tds version = 8.0

With the following PHP statement:

  $db=mssql_connect('connect2k5','usrxxxx','pwdxxxx');

thedeathart at gmail dot com
29-Nov-2007 06:02


Undocumentated note:



PHP does not support Microsoft SQL Server 2005 Express, because php use the old ntwdblib connection, which MSSQL 2005 Express don't support.



And since there is no contributers on both this driver, and the PDO driver, it haven't been updated to support it for the almost 2 years MSSQL 2005 Express been out.



Neither will PHP support MSSQL 2008.

dkman one two three at hotmail dot com
27-Nov-2007 12:01


Using: Apache 2.2

PHP 5.2.5

SQL 2005 (on a separate pc)



Getting error:

Call to undefined function: mysql_connect()...



Fixed by:

copy ntwdblib.dll from php to apache\bin

copy php_mssql.dll from php\ext to apache\bin



Then I received a timeout error, but at least it tried to connect:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server...



Fixed by:

Following a prior suggestion I downloaded a new version of ntwdblib.dll from Webzila (with one L).  You need to enable Javascript and click on downloads, then search.

Put the new ntwdblib.dll (2000.80.194.0) in the apache\bin directory

Restart the apache service



Then I was able to connect!



SQL Notes:

SQL should be in mixed mode (authentication)

I enabled named pipes as others described:

1) On the SQL Server go into "SQL Server configuration Manager" from the start menu.

2) Click SQL Server 2005 Network Configuration

3) Click Protocols for [YOUR SQL]

4) Enable Named Pipes



PHP Notes:

Edit the INI file to uncomment extension=php_mssql.dll

The default for secure is mssql.secure_connection = Off, I left this alone.

chop_01 at yahoo dot com
29-Aug-2007 01:39


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>

Colin
16-Aug-2007 03:35


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.

mariodivece at yahoo dot com
19-Jul-2007 09:53


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

hao dot wang at outsourcing dot org
18-Jul-2007 05:04


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.

streamkid {) at (} gmail {) dot (} com
16-Jul-2007 12:55


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

animefan1003 at yahoo dot com
07-Jul-2007 09:32


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

Anders Simonsen
02-Jul-2007 09:21


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

rossco at hotpop dot com
24-May-2007 09:04


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)

daslsack at hotmail dot com
27-Apr-2007 03:51


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

}

raul at tarkus dot com dot mx
09-Dec-2006 03:19


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

twiddly
02-Dec-2006 08:46


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.

robajz _ at _ robajz _ dot _ info
23-Nov-2006 06:50


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.

jan.gehring [at] gmail [dot] com
09-Nov-2006 01:32


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

grv575 at hotmail dot com
03-Nov-2006 07: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 01: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.

28-Oct-2006 04: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
14-Oct-2006 06: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
23-Sep-2006 01: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 08: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)
23-Aug-2006 12: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
13-Jul-2006 10: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
31-May-2006 09: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 12: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
01-May-2006 01: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 03: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
06-Jan-2006 09: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
13-Dec-2005 08: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
03-Dec-2005 03: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
15-Nov-2005 07: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 06: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
17-Oct-2005 08: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 05: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
19-Sep-2005 12: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
27-Jul-2005 01: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
13-Jun-2005 06: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 07: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
05-Jun-2005 04: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 05: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
17-May-2005 12: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 01: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 01: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 07: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
09-Mar-2005 07: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 02: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
28-Jan-2005 11: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
23-Jan-2005 05: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 06: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
18-Jan-2005 09: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 04: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
13-Jan-2005 11: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 05: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 06: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
13-Dec-2004 02: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
17-Nov-2004 07: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
18-Oct-2004 05: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
02-Oct-2004 04: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 05: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 06: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 05: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 07: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
19-Feb-2004 07: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 07: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
17-Jan-2004 11: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!

jack at mvps dot removethis dot andthis dot org
09-Dec-2003 04: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
12-Jul-2003 05: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 03: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
03-Jun-2003 07: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());

Gordon McCague
12-Feb-2003 11: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
30-Jan-2003 01: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
17-Jan-2003 07: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!

cdthompso1 at yahoo dot com
19-Nov-2002 10: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
13-Nov-2002 01: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
12-Nov-2002 04: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
03-Sep-2002 05: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...

add a note

mssql_data_seek

mssql_close

Last updated: Sun, 25 Nov 2007