Solved

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

Posted on 2014-03-09
8
2,905 Views
Last Modified: 2014-03-09
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
Comment
Question by:GEMCC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39915929
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39916017
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39916041
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39916060
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
 

Author Comment

by:GEMCC
ID: 39916189
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
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39916197
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39916231
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
 

Author Closing Comment

by:GEMCC
ID: 39916252
Able to login successfully.  Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question