This project is read-only.
1
Vote

Domain user problem

description

Hey guys,

I'm testing your extension and I found this:

When I try to connect using a domain user account (DOMAIN\USER) it says it can't connect "access denied" even when the user has admin rights on that server (DBA), but it does work when a regular user (NOT DOMAIN) logs in, any ideas what can be causing this?

Thanks.

comments

jfha73 wrote Oct 8, 2013 at 7:32 PM

Just to clarify, the message is not "access denied" it is "login failed for user DOMAIN\USER"

icosahedron wrote Oct 22, 2013 at 5:44 PM

Interesting. Can you show the exact PHP line? I'm wondering if it might require double backslashes?

Jay

icosahedron wrote Oct 22, 2013 at 5:44 PM

Also, can you connect to the database from SSMS or sqlcmd?

jfha73 wrote Oct 22, 2013 at 9:44 PM

This is what I have to test connection:
<?php
$serverName = $_POST['server']; // serverName\instanceName
$connectionInfo = array( "Database"=>$_POST['dbName'], "UID"=>$_POST['username'], "PWD"=>$_POST['password'] );
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "<h2>Connection established.</h2>";
     sqlsrv_close( $conn );
}else{
     echo "<h2>Connection could not be established.</h2>";
     die( print_r( sqlsrv_errors(), true));
}
?>
I will try with double "\" and I can connect just fine using any other way to connect.

jfha73 wrote Oct 22, 2013 at 9:45 PM

I forgot to say it gets the variables from a form.

jfha73 wrote Oct 22, 2013 at 9:47 PM

I tried using DOMAIN\User and I get same error.

jfha73 wrote Oct 22, 2013 at 9:49 PM

DOMAIN\User (double backslash)

jfha73 wrote Oct 22, 2013 at 9:50 PM

weird, I put double backslash on this forum and it shows 1

robertjohnson wrote Oct 23, 2013 at 10:07 AM

You cannot log-in with Windows authentication using a UID and PWD. The UID and PWD are for SQL Server credentials. Windows authentication is trusted, you don't send the UID - it gets it from your session. That's what it is.

With PHP, the Windows ID comes from the PHP instance. Why not check the forum where this has been discussed several times.

jfha73 wrote Oct 24, 2013 at 1:16 AM

Doesn't that depend on what Browser and Server you use?

I know Windows Authentication can be used using IIS and IE, but in my case I use Apache and Firefox, does that apply in my case?

Thanks.

jfha73 wrote Oct 24, 2013 at 1:23 AM

Also, how about if I want to login as another user? for example, I have 2 accounts, one that's a regular user with no admin rights to anything but my computer and a second one that has admin rights for everything in the domain, I am at my computer logged in with my regular user account and I want to login using SQLSRV with my admin account?

It just doesn't make sense to me.

robertjohnson wrote Oct 24, 2013 at 9:34 AM

PHP will use the credentials that Apache starts with for trusted login. So when you call sqlsrv_connect without a UID and PWD, the connection will be attempted with most likely your computer's system account.

You could either:
  • add a Windows login on SQL Server for the computer's system account that Apache is starting as.
  • set the start-up account for Apache to the same account that has access to SQL Server.
To be honest with you I think SQL Server credentials are the better route with PHP, then you can set up as many different accounts as you want and not have login issues when you deploy to different computers.

It does make perfect sense - with trusted login there is no password exchange, which would weaken the whole process of logging in. It just isn't suited to server side processes that may need to impersonate different accounts, but don't have a built-in/secure way to do that *.

(* if you were an experienced Windows developer you could make a COM class that impersonates another Windows user - any SQL connections you make while impersonating will use the impersonated credentials, and stay that way even after you stop impersonating - so to connect as a different Windows user, you: impersonate... connect with trusted login... stop impersonating... use your connection. However, it defeats the purpose of trusted login, and is not very different to using SQL Server credentials).