Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)
(PHP 4, PHP 5, PECL odbtp >= 1.1.1)
mssql_connect — Baut eine Verbindung zum MS SQL Server auf
mssql_connect() baut eine Verbindung zu einem MS SQL Server auf.
Die Verbindung zum Server wird geschlossen, sobald die Ausführung des Skripts abgeschlossen ist, wenn sie nicht schon vorher durch einen expliziten Aufruf von mssql_close() geschlossen wurde.
Der MS SQL-Server. Er kann auch eine Portnummer enthalten, z.B. hostname:port (Linux) oder hostname,port (Windows).
Der Benutzername
Das Passwort
Falls mssql_connect() zum zweiten Mal mit denselben Argumenten aufgerufen wird, wird keine neue Verbindung aufgebaut, sondern stattdessen die Verbindungskennung der bereits bestehenden Verbindung zurückgegeben. Mit diesem Parameter wird dieses Verhalten geändert, sodass mssql_connect() immer eine neue Verbindung aufbaut, auch wenn mssql_connect() zuvor schon mit denselben Parameter aufgerufen wurde.
Gibt bei Erfolg eine MS SQL Verbindungskennung oder bei Auftreten eines Fehlers FALSE zurück.
| Version | Beschreibung |
|---|---|
| 4.4.1 und 5.1.0 | Den Parameter new_link hinzugefügt |
Beispiel #1 mssql_connect()-Beispiel
<?php
// Server in diesem Format: <computer>\<instance name> oder
// <server>,<port>, falls nicht der Standardport verwendet wird
$server = 'KALLESPC\SQLEXPRESS';
// Mit MSSQL verbinden
$verbindung = mssql_connect($server, 'sa', 'phpfi');
if (!$verbindung) {
die('Beim Aufbau der Verbindung mit MSSQL ging etwas schief');
}
?>
I didn't get it working with PHP 5.2.9 and MS SQL 2008, so I used this article http://msdn.microsoft.com/en-us/library/cc793139(SQL.90).aspx and with SQL login (not Windows authentication!) it works fine!
By far the easiest way that I've found to connect to a SQL server is to route it through a ODBC connection.
1. Administrative Tools -> Data Sources (ODBC)
2. System DNS
3. Add... "SQL Server"
Then just use odbc_connect("sql_test", "user", "pass") instead.
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.
If you have an application which requires MSSQL as interface (and not the new SQLSRV) you have to look for an alternative method. The old (now rightly unsupported) way from Microsoft does not work with PHP 5.3.
After spending several days and nights with trying to connect
here is an easy solution which worked great with all applicatins expecting mssql: FreeTDS for Windows. Here is a great how to with installer links on the Moodle site: http://docs.moodle.org/en/Installing_MSSQL_for_PHP
Couple of hints:
- Make sure you have set up a user with "SQL SERVER authentication" who has rights to connect.
- Also make sure after you install and still have problems to run PHPinfo and check that you find the below:
PHPINFO: mssql
MSSQL Support enabled
...
Library version FreeTDS
That works perfect for me on Windows 2003 with IIS6 and PHP 5.3 (non threadsafe, VC9).
Hope this is helpful
On a windows 2000 server, i have php 2.6 and apache
It's been 2 days i'm trying to connect it to mssql server 2K5.
i tried everything, of course getting the last version of the sql library (ntwdblib.dll), check the permissions, the dll version etc... but no way....
Finally, i installed the last version of MDAC 2.8
(there's an old one on win2000, even with all the updates...)
And now it works perfectly :-))
When you try to connect to SQL Server 2008 Express, you always have to name the instance you are trying to connect to, even if you installed SQL2008express as "Default" instance instead of named instance. If your instance is SQLExpress and server name is myServer, the server parameter must be: "myServer\SQLExpress". If you installed Default instance of Standard (not express) version of SQL2008 instead, you don't need to name the instance. To connect using TCP/IP, you need also to check server network configuration, enable TCP/IP (listen all: yes), then go to addresses tab and setup port 1433 in all addresses you want the server listen on (by default, port is blank and server don't listen). Libraries involved in TCP/IP are ntwdblib.dll and dbnetlib.dll. Have fun!
After months of searching, I have discovered the most simple way of connecting Php to MS Sql Express / Enterprise:
-- Use the Microsoft SQL Server Driver for PHP! --
1) Download from http://tiny.cc/fkGzf
2) Place php_sqlsrv_ts.dll in your php extensions folder.
3) Enable php_sqlsrv_ts.dll in your php.ini -> extension=php_sqlsrv_ts.dll
4) Follow the documentation and api reference at http://msdn.microsoft.com/en-us/library/cc296152%28SQL.90%29.aspx
This works on apache, lighttpd, wampserver (haven't tested on IIS).
You no longer need to worry about the ntwdblib.dll or whether your web server is in FastCGI mode or not.
Absolutely simple and it connects straight away!
Just remember to use the correct server name: $serverName = "<computer name>\SQLEXPRESS";
For my setup (Windows Server 2003, IIS 6, PHP 5.2.6, SQL Server 2005 Enterprise) the key to getting a successful connection using mssql_connect was changing PHP from ISAPI mode to FastCGI mode.
1) I downloaded and installed FastCGI for IIS 6 from Microsoft http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1521
2) I modified my PHP install so it runs in FastCGI mode.
3) Opened fcgiext.ini and added this to the end.
[Types]
php=PHP
[PHP]
ExePath=c:\PHP\php-cgi.exe
InstanceMaxRequests=10000
EnvironmentVars=PHP_FCGI_MAX_REQUESTS:10000
4) I added cgi.force_redirect=0 to the “; Default timeout for socket based streams (seconds)” section of PHP.ini
5) I made sure my SQL database was set to Windows authentication mode and I also switched mssql.secure_connection in php.ini to on.
6) Created an SQL login for NT AUTHORITY\NETWORK SERVICE and gave it the appropriate permissions to my database.
7) Rebooted the server
I was trying to connect to a remote SQL2000 server (located on Internet, not on the local network), then php always returned an "Unable to connect" error or something.
After waste a lot of time, by searching among foruns, blogs and logs, I finally discovered the problem. I was monitoring the TCP connections between the server and my machine when I realized that it wasn't been done. For some reason, my Apache 2.2 was "denying" php module from doing remote connections.
So I changed the php configuration on apache to CGI mode rather than as module mode. Well, it worked fine since that.
In despite of security holes that it involves, it was the only way I've found to make that remote connection works between Apache2.2/PHP5 and MSSQL Server 2000.
Strangely, connections inside local network worked in any case (in CGI or module mode).
Just a remind: Although it is not present here in this page, the parameter **$new_link** holds for mssql_connect() and mssql_pconnect(). The correspondent mysql functions documentation describes it though.
Just a remind: Although it is not present here in this page, the parameter **$new_link** holds for mssql_connect() and mssql_pconnect(). The correspondent mysql functions documentation describes it though.
After a great deal of testing various things I finally found a solution for connecting to a named instance of MS SQL Express. I first had to download version 200.80.194.0 of ntwdblib.dll and overwrite the existing version in c:\PHP. I then tried an instance name that was posted by "chop_01 at yahoo dot com" in this thread. The instance name is ".\SQLEXPRESS".
For example:
<?php
$dbconnect = mssql_connect(".\SQLEXPRESS", $dbUser, $dbPass);
?>
For anyone who have to connect to crappy SQL SERVER with \\computer_name\instance_name and failed to connect. There are couple of thing you need do.
1). There's no needs to install SQL client software in your app server. This would keep your server clean.
2). run cliconfg.exe setup alias in case you needed
3). last and most important, do NOT use ntwdblib.dll came with PHP 4.4.x. It's not most current. The version I'm using is: 8.00.194 with 274,489 Bytes. And make sure you put this file in your system32 folder.
Hello,
My solution was to install MSSQL Server Native Client
Info:
http://msdn.microsoft.com/en-us/data/aa937733.aspx
System:
W2003 w/PHP 5.2.1 and SQL Server 2008 on Win 2008
(Should also work on 2005)
My problem was that mssql_connect() failed.
Installed the native client, restarted IIS and everything went smoothly.
Helge Hetland
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()).
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
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.
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
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.
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
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');
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>
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.
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.
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
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 :-) .
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
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)
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
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.
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.
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.
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.
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.
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
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
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
<sarcasm> Who would have thought that microsoft's article would be useful </sarcasm>
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.
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.
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);
?>
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.
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
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!).
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.
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 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
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
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.
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
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 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 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.
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!
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
<?php
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
*/
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.
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
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!
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
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
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.
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 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.
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!
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.
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...
Diese Seite bei php.net