PHP and MSSQL

Question

PHP and MSSQL

ram302

SSCommitted

Points: 1618
More actions
April 19, 2016 at 3:15 pm

#313423

I have the following code that doesn't seem to make the connection to the MSSQL db:
<?php
$serverName = "SERVER_NAME\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"TESTDB",
"UID"=>"SERVER_NAME\Administrator",
"PWD"=>"Password123");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn )
{
echo "Connection established.";
}
else{
echo "Connection could not be established.";
die( print_r( sqlsrv_errors(), true));
}
?>
The message reads that login failed for user SERVER_NAME\Administrator, but that's what I see in the SQL Server Management Studio when I check the DB properties. I also checked the connection properties from SQL Server Management Studio to confirm the user that connects to the DB. Any help is greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

I haven't done the PHP connection, but users are usually not listed as server\administrator for a SQL login. Usually you would put in just Administrator.

Make sure you don't have the password policy with change password checked. That might cause you issues on the initial connection as some apps don't handle the password change well.

ram302 SSCommitted Points: 1618 More actions · Answer 2

Thanks, Steve.

1. I have tried also without the server name preceding 'Administrator' to no avail.

2. Where would I find this password policy?

ram302 SSCommitted Points: 1618 More actions · Answer 3

Ok,

I created a login under the server properties > security. Added this user in TESTDB > Security.

For this user, I should only grant it access to Connect and Select, right?

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

Connect usually comes with the login creation.

Password policy is under the login properties, or here:

select

LOGINPROPERTY(log.name, N'IsLocked') AS IsLocked

, LOGINPROPERTY(log.name, N'IsExpired') AS IsPasswordExpired

, LOGINPROPERTY(log.name, N'IsMustChange')

In terms of user rights, you'll need to grant what you need to each object. I'd recommend you use a role, as if you do this for one user, you might for another. For example, you might test this with other accounts.

Create a role, and add this user.

CREATE ROLE WebPHPApp;

go

alter role WebPHPApp add member 'Joe'

go

grant select on mytable to WebPHPApp

ram302 SSCommitted Points: 1618 More actions · Answer 5

Thanks!

One more thing, I hope, I'd like to see what the 'CHAR SET' is for my database. How can I do this? I'm seeing an error that reads:

Unable to set client connection character set: utf8

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 6

Not sure what this means. I assume you mean collation, which probably maps to Charset somehow. This would be SERVERPROPERTY('Collation'), but this can be set at the database and table levels as well.

Looks like UTF-8 isn't directly supported: http://dba.stackexchange.com/questions/7346/sql-server-2005-2008-utf-8-collation-charset

This mght help: https://msdn.microsoft.com/en-us/library/cc626307%28v=sql.105%29.aspx

ram302 SSCommitted Points: 1618 More actions · Answer 7

Thanks for the lead. I tried the solution on Microsoft's website, but still no dice. This seems to be an issue isolated to CodeIgniter, the MVC I planned on using, since it's working ok in plain PHP. I've contacted them for help, but for now, I'll just be writing a new connection class altogether.