Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2014-03-09
8
Medium Priority
?
3,686 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 2000 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 84

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 84

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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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 84

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Laravel is the most sought after web development framework. It comes with ample amount of features that make it easy for developers to work around it. Know about its features in detail.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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