CODEWITHSUNDEEP
sql-serversql-server-2008phpwindowsodbc
Compysaurus
Compysaurus

Reputation: 191

Connecting to SQL Server using PHP, ODBC, and Windows authentication

I'm trying to use PHP to connect to an ODBC data source using Windows authentication. I can connect just fine to the server in SQL Server so I know it's running. When I try to run the command 

$link = odbc_connect("my_odbc","","");

I get the error:

"Warning: odbc_connect(): SQL error:
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]
Login failed for user ''., SQL state 28000 in SQLConnect in C:\Users..."

I tried: 

$link = odbc_connect("Driver={ODBC Driver 11 for SQL Server};   
        Server='my_odbc';Integrated Security=SSPI","","");

Which returned the message:

Warning: odbc_connect(): SQL error:
[Microsoft][ODBC Driver 11 for SQL Server]
Named Pipes Provider: Could not open a connection to SQL Server [53]. , SQL state 08001 in SQLConnect in C:\Users..."

Not sure what I'm doing wrong.

Upvotes: 11

Views: 41835

Answers (7)

Zellmer
Zellmer

Reputation: 11

You have to create an ODBC interface first. In principle, this should also work without a user and pw. If defined in your interface.

$pdo = new PDO('odbc:dsnName','user','pw');

Upvotes: 0

Luis D
Luis D

Reputation: 1

Enable in IIS on Manager PHP o PDO_ODBC:

I have authenticated by Windows with following PHP statement:

$ Conn = new PDO ("odbc: Driver = {SQL Server}; Server = JAMILI-PC \ SQLEXPRESS; null; null");

I am using Windows 2008.

I hope it solves your problem.

Upvotes: -2

Emre Karataşoğlu
Emre Karataşoğlu

Reputation: 1719

Look at the regedit HKLM/SOFTWARE/ODBC

what is your folder name ?

If there is a "SQL Server Native Client 11.0" then you have to write your php code like 

odbc_connect("Driver={SQL Server Native Client 11.0};Server=ip;Database=db;", "user", "pass");

However some of the server like server 2008 will be record this odbc like 

ODBC Driver 11 for SQL Server

Then you have to replace SQL Server Native Client 11.0 by ODBC Driver 11 for SQL Server.

Upvotes: 0

aimme
aimme

Reputation: 6773

This error is a General Error that arises due to unable to establish connect to the server for some reasons. Its important to know what server are you using with PHP and the application used with it. Example Apache, Xampp or Wamp etc.

Here are somethings you could try like.

Let me brief, below are the reference links.

  • Check whether it pings
  • Enable TCP/IP connection SQL Server Configuration if not enabled. To do so, Open SQL Server Configuration Manager -> SQL Server Network Configuration->Protocols for SQL server->TCP/IP(set to enabled). Restart SQL services.
  • Enable Remote Connection from server
  • Open the Port, To do so Windows Firewall Settings-> Exceptions -> add a Port (Name:SQL;Port:1433;TCP) then from Exceptions tick SQL and save.
  • Enable running browser services.SQL Server Configuration Manager -> SQL Server Services - > SQL Server Browser set to running.
  • defining Port in connection string
  • add instance name with machine name if more than one instance is being used

sample

$user = 'username';
$pass = 'password';
//Use the machine name and instance if multiple instances are used
$server = 'serverName\instanceName';
//Define Port
$port='Port=1433';
$database = 'database';

$connection_string = "DRIVER={ODBC Driver 11 for SQL Server};SERVER=$server;$port;DATABASE=$database";
$conn = odbc_connect($connection_string,$user,$pass);

here are useful links that i found regarding the issue

resolving could not open a connection to sql server-errors

sql server provider named pipes provider error

Have a look at these links too

Named Pipes Provider: Could not open a connection to SQL Server [53]

Upvotes: 3

timclutton
timclutton

Reputation: 13004

A significant omission from your question is how you are running PHP and with which credentials.

Assuming you are using IIS or Apache (or another web server), then your PHP process is probably running under the local system account:

IIS service properties Apache service properties

Since this account is local it doesn't have any authorisation to access your remote SQL Server.

You could alter the IIS/Apache service to run with the credentials (yours?) that are authorised to connect to SQL Server, but be aware this might cause other permissions issues with the service as well as being a problem if you change your password in the future.

Try running a test script from the command line (which should run with your credentials by default) to determine where the problem is:

php -r "var_dump(odbc_connect(...));"

Upvotes: 2

Jonathan Parent Lévesque
Jonathan Parent Lévesque

Reputation: 1950

Maybe you should try PDO (the performance difference isn't that great) with SQLsrv plugin (this what I'm using to connect to my other boss' software which use SQL Server 2008 database):

$connection = new PDO("sqlsrv:Server=" . $this->sourceServer . ";Database=" . $this->sourceDB, $this->sourceUser, $this->sourcePW);
$connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

You can download the plugin here

https://www.microsoft.com/en-ca/download/details.aspx?id=36434

In Xampp, you must copy the dll in that folder:

C:\xampp\php\ext

And you must add that line to your php.ini

extension = php_pdo_sqlsrv_56_ts.dll

Note: Don't forget to restart your server so it can take in account the new php.ini file.

Let me know if it works for you

Upvotes: 3

ourmandave
ourmandave

Reputation: 1585

Someone had the same error message and asked and answered his own question here.

To quote...

I´m sorry for the troubles.

The problem was, that I was using SQL Server Native Client 11.0 as driver. I switched it to SQL Server and now it works :/

Hopefully this at least helps someone, being in a similar problem....

Upvotes: 2

Related Questions