[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3536
  • Last Modified:

Connection failed: SQLSTATE[IMSSP]: An invalid keyword 'host' was specified in the DSN string.

Hello,

I am getting this error when trying connect from PHP to MS SQL.  Running MS SQL 2012

Here is what I have:

<?php
$dsn = 'sqlsrv:host=servername\SQLName,1433;dbname=DSN_Name';
$user = 'User';
$password = 'Password';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>

What am I doing wrong?

Thanks and have a great day,

Don
0
GEMCC
Asked:
GEMCC
  • 3
  • 3
  • 2
1 Solution
 
Dan CraciunIT ConsultantCommented:
Try with:
$dsn = 'sqlsrv:Server=servername,1433;Database=database_name';

Open in new window

or
$dsn = 'sqlsrv:Server=servername\\SQLName,1433;Database=database_name';

Open in new window

HTH,
Dan
0
 
Dave BaldwinFixer of ProblemsCommented:
You're using a format for an ODBC connection string.  None of those are correct for the 'sqlsrv' driver.  Here's the first example on the PHP 'sqlsrv' man page.  There are several others on that page including ones that have username and password.
<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"dbName");
$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));
}
?>

Open in new window


http://us3.php.net/manual/en/function.sqlsrv-connect.php
0
 
Dave BaldwinFixer of ProblemsCommented:
After re-reading your question... here's the page for PDO_SQLSRV connections.  http://us2.php.net/manual/en/ref.pdo-sqlsrv.connection.php
$c = new PDO("sqlsrv:Server=localhost;Database=testdb", "UserName", "Password");

Open in new window

And as is pointed out, '\' in a double quoted string needs to be escaped to '\\'.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Dan CraciunIT ConsultantCommented:
Dave, if he wants to use PDO, this page: http://us3.php.net/ref.pdo-sqlsrv.connection says you have to format the data source name as per my previous answer.

Is there something I'm missing and you cannot use PDO to connect to MS SQL?

LE: I took too much to write and you already answered :) I really should learn to refresh the page before hitting submit...

Dan
0
 
GEMCCAuthor Commented:
Hi,

Using Dan's suggestion (either one), I am now getting:

Connection failed: SQLSTATE[28000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'gem-app'.

I am attempting to use a Windows login (which I do not really want to use, suggestions?).  For the Windows logon I have tried both the username and domainname\username.

Any ideas?

Have a great day,

Don
0
 
Dan CraciunIT ConsultantCommented:
That error says your code is valid, but the user/pass combo does not work. Can you connect to the administrative console using those credentials?
0
 
Dave BaldwinFixer of ProblemsCommented:
To use a Windows Auth login, 'gem-app' has to be a user logged into the computer where the SQL Server is located or possibly an AD user on an AD domain.  On all of my SQL Server installs, I have enabled remote TCP/IP connections and created a user with limited permissions and connected mixed / SQL Auth.  

If you are going to use the "sqlserver\instance" format you have to have the 'sqlbrowser.exe' service running on the machine with the SQL Server because that is what makes the 'instance' connection.  Otherwise, you can use the IP address to connect to port 1433 or whatever port is setup for that server.
0
 
GEMCCAuthor Commented:
Able to login successfully.  Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now