Connecting to SQL Server 2008 through PHP

Question

I need to connect to a SQL Server 2008 through PHP (WAMP, latest version). I have the sqlsrv drivers installed and set up and they do show up in phpinfo().

I'm using the following lines to connect to my DB, using Windows Authentication:

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

And I'm getting 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 since I really don't know my way around WAMP except for a few basics.

Answer 1

Based on your setup, you may need to revert to the mssql_connect and corresponding functions. This works fine with mssql 2008 and you don't usually lose meaningful functionality.

Here's an example of setting up a connection to the database. Depending on your configuration you may have to add port information, etc.

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

If you are running on a XAMP environment, then it would stand to reason that you are running everything locally. If that's the case, your server would 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 in SQL Server Management tool set that user to have access to the DB you are using. Then you have set the account and password and know what they are. If you have trouble with the user account, you can try computerName\userName.

If you are not the admin, you'll need to get the relevant information from them.

Answer 2

sqlsrv_connect PHP manual

Try this approach to see what the error actually 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

The error is caused by a permission issue in the registry. There is a key where the path to the native client is stored and the identity you are using in the application pool is getting denied access.

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

• Find the registry key where the process w3wp.exe is being denied access. That in the case of IIS, not sure what's the name of the process 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 go to Permissions
• Add Network Service to the list and give it Read permissions.
• Recycle the app pool and it should work