Connecting to SQL Server 2008 through PHP

Question

I need to connect to SQL Server 2008 through PHP (WAMP, latest version). I have sqlsrv drivers installed and configured, and they appear in phpinfo() .

I use the following lines to connect to my database using Windows authentication:

 $serverName = "(local)"; $connectionOptions = array("Database"=>"MyTestDatabase"); $conn = sqlsrv_connect( $serverName, $connectionOptions) or die("Error!"); 

And I get the following error:

 Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code] => -49 [2] => This extension requires the Microsoft SQL Server 2011 Native Client. Access the following URL to download the Microsoft SQL Server 2011 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 [message] => This extension requires the Microsoft SQL Server 2011 Native Client. Access the following URL to download the Microsoft SQL Server 2011 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 ) [1] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) ) 

Any help would be great, but please be specific, because I really don't know my way around WAMP, except for a few basics.

Answer 1

The error is caused by a registry permission problem. There is a key in which the path to your own client is saved, and the identity that you use in the application pool is denied access.

• Download Process Monitor and follow the instructions on this post: http://www.iislogs.com/articles/processmonitorw3wp/ (this guide shows how to do this in IIS, with WAMP you will need to find the .exe process that runs in memory )

• Locate the registry key in which the w3wp.exe process is accessing. What is the case with IIS, not sure if the process name is in WAMP, but the procedure is the same. In my case:

   HKLM\Software\ODBC\ODBCINST.INI\SQL Native Client 10.0 

• Run regedit and find the key
• Right-click and select Permissions.
• Add a network service to the list and grant it read permissions.
• Restart the application pool and it should work

Answer 2

sqlsrv_connect PHP Guide

Try this approach to see what the error is:

 $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn ) { echo "Connection established.<br />"; } else { echo "Connection could not be established.<br />"; die( print_r( sqlsrv_errors(), true)); } 

Answer 3

Based on your setup, you may need to revert to mssql_connect and related functions. This works great with mssql 2008, and you usually do not lose significant functionality.

Here is an example of setting up a database connection. Depending on your configuration, you may need to add port information, etc.

 $hostname = "server.domain.com"; $database = "DatabaseName"; $username = "LocalSQLUserName"; $password = " P@ssw0rd "; $Connection = mssql_connect($hostname, $username, $password); 

If you work in the XAMP environment, this will mean that you are doing everything locally. In this case, your server will be localhost or 127.0.0.1 , and you can define your own accounts in SQL. As I said, I don’t use Windows accounts myself, but you can add an NT account to your “server” and then set it in the SQL Server management tool so that the user has access to the database you are using. Then you set up an account and password and know what they are. If you are having problems with your user account, you can try computerName\userName .

If you are not an administrator, you need to get relevant information from them.