Solved

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

Posted on 2014-03-09
8
2,830 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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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.

773 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