[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

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,235 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 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

656 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