none
Connection to SQL Server 2008 - Login failed for user

    Pertanyaan

  • Hi

    I have setup a PHP webpage and am trying to connect to an SQL Server 2008 on another machine.

    I have installed 'Microsoft Drivers for PHP for SQL Server' on my local machine which also has WAMP installed with PHP version 5.3.5.

    I am getting the following error:

    array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\Ryans'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\Ryans'. ) [1] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\Ryans'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\Ryans'. ) )'

    I have setup the user under security - logins on the server.

    The code i am using to connect to the server is -

    $serverName = "crm\MSSQLSERVER,1433";
    
    
    $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=> $servername);
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    if( $conn )
    {
         echo "Connection established.\n";
    }
    else
    {
         echo "Connection could not be established.\n";
         die( print_r( sqlsrv_errors(), true));
    }


    Any help would be much appreciated.

    Regards Ryan

    08 Desember 2011 16:14

Jawaban

  • Hi

    You cannot login to the sqlsrv driver as a Windows user the way you are doing it.

    You supply UID/PWD connection string credentials to log in as a registered SQL Server user - i.e. a login that is created on the server, with the SQL Server Authentication option set.

    It looks like DOMAIN\ryans is a Windows domain account.  You need a trusted connection.

    When you want to log in with Windows credentials, you do not supply UID and PWD at all.  Instead you need to set up PHP to run as the user you want to connect as.

    This can be achieved using IIS with Windows security ** , or running the web site in a domain* that connects with the required credentials.

    *
    1. On Apache, run the Apache service as the user that connects to SQL Server.
    2. On IIS, set up an application pool with the required credentials.

    **
    If using fast-cgi, you can set fastcgi.impersonate=1, then set the web site to disable anonymous access + use Windows authority.  IIS/fastcgi will do the job of running your PHP connection under the credentials of the user on the client end of the site.  Setting fastcgi.impersonate=0 would always connect as the App Pool user credentials.  Choose which you prefer. (p.s if it's not quite the same as I've described it, please experiment with the settings and let us know what you discover.)

     

    At the present time I believe it is most flexible to supply SQL Server credentials to the PHP driver.

    As a footnote, if you are a C++ programmer, you could write a COM class that has a method to log-in your NT account, impersonate it, then create your connection to the database while impersonating.  This approach should be a last resort, with quite a lot of overhead.

     


    Rob
    13 Desember 2011 13:19

Semua Balasan

  • I have left out the username, password and server name variables for obvious security reasons
    08 Desember 2011 16:15
  • In your $connectionInfo array you are setting "Database"=>$servername. Does $servername actually contain the database name (as it should)?

    If you have created a login for DOMAIN\Ryans on your server, you will also need to make that login a user for the database you are trying to connect to.

    Hope that helps. If not, we'll look further.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    08 Desember 2011 17:26
  • Hi

    Thanks for your help but I am still having the same issue.

    I've changed the following line so its easier to understand - 'Database"=>$dbname' and the corresponding variable.

    DOMAIN\ryans has a login on to both the server and database. I am pretty sure it should be a lowercase r. One issue I can see with a lower case r is in the error message -

    Login failed for user 'DOMAIN yans'

    The '\r' is missing. To make the full username display i have to put the username in single quotes.

    I created another user in the database to see if using those credentials worked.....it didnt. (Although for some reason this user couldnt remote onto the server through RPC, despite having full admin rights.)

    Thanks

    Ryan

    12 Desember 2011 10:32
  • Oh...you are running into a PHP feature. If you enclose a string in double quotes, then \ escapes the following character. My guess is that you are defining $uid like this:

    $uid = "DOMAIN\ryans";

    Try it with single quotes:

    $uid = 'DOMAIN\ryans';

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    12 Desember 2011 18:15
  • Hi

    I tried it using single quotes and I still get the same error message -

    Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\ryans'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\ryans'. ) [1] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\ryans'. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DOMAIN\ryans'. ) )

    13 Desember 2011 10:54
  • Hi

    You cannot login to the sqlsrv driver as a Windows user the way you are doing it.

    You supply UID/PWD connection string credentials to log in as a registered SQL Server user - i.e. a login that is created on the server, with the SQL Server Authentication option set.

    It looks like DOMAIN\ryans is a Windows domain account.  You need a trusted connection.

    When you want to log in with Windows credentials, you do not supply UID and PWD at all.  Instead you need to set up PHP to run as the user you want to connect as.

    This can be achieved using IIS with Windows security ** , or running the web site in a domain* that connects with the required credentials.

    *
    1. On Apache, run the Apache service as the user that connects to SQL Server.
    2. On IIS, set up an application pool with the required credentials.

    **
    If using fast-cgi, you can set fastcgi.impersonate=1, then set the web site to disable anonymous access + use Windows authority.  IIS/fastcgi will do the job of running your PHP connection under the credentials of the user on the client end of the site.  Setting fastcgi.impersonate=0 would always connect as the App Pool user credentials.  Choose which you prefer. (p.s if it's not quite the same as I've described it, please experiment with the settings and let us know what you discover.)

     

    At the present time I believe it is most flexible to supply SQL Server credentials to the PHP driver.

    As a footnote, if you are a C++ programmer, you could write a COM class that has a method to log-in your NT account, impersonate it, then create your connection to the database while impersonating.  This approach should be a last resort, with quite a lot of overhead.

     


    Rob
    13 Desember 2011 13:19
  • Hi Rob

    I tried to connect using IIS with Windows security. When I load the page a window pops up asking me for a username and password. It doesnt accept anybody creditentials.

    I get the following error message -

    HTTP Error 401.1 - Unauthorized

    You do not have permission to view this directory or page using the credentials that you supplied.

     

    I also installed PHP on a Windows Server 2008 and ran the file on the server and i am still getting the same error message.

    Anything else you can suggest?

    Thanks




    • Diedit oleh rstalbow 14 Desember 2011 12:40
    14 Desember 2011 12:30
  • Hi Ryan

    That's just one option but it looks like you need anonymous access, so restore IIS to use Authentication - Anonymous.

    Then go to your site's application pool, select it and click on 'Advanced Settings', and then look for a setting called 'Identity'. 

    You have choice of 2 things, choose one:

    1. Put DOMAIN\ryans here, and make sure your account has enough permission to run the whole site.  The site (access to files and directories) and database connection will be made with your domain account.
    2. Make a note of the identity, and create a login on your SQL Server for the existing app pool identity (e.g. ApplicationPoolIdentity) with permissions to your database for the identity... see here: http://forums.iis.net/t/1162459.aspx for how to do it using ApplicationPoolIdentity, which is a special type of built-in account.

    Then back in your PHP app, connect to the database without the UID and PWD connection parameters.


    Rob
    15 Desember 2011 14:38
  • Hi Rob

    I tried both of those ideas and I still cannot connect to the database.

    For point 1 i just get specified password invalid. I had trouble following point 2.

    Ryan

    03 Januari 2012 11:19
  • Ryan,

    If you get specified password is invalid, it sounds like you are still trying to use UID and PWD. Can you please verify that you are not using them...?

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.
    03 Januari 2012 17:30
  • No i am not using the UID and PWD variables. A windows login box pops up asking for username and password. I type in my username and password and get password invalid.

     

    Ryan

    05 Januari 2012 10:29
  • That sounds like you are still using IIS Windows Authentication. You need to disable Windows Auth and enable Anonymous Auth. Then, the trick to to make sure that the credentials passed to SQL Server match a login defined on the server. As Rob pointed out, you have two options:

    1. Set the credentials of your application pool to DOMAIN\ryans (assuming that corresponds to a login on the server).

    2. Make a note of the credentials under which the application pool is running, and create a login on the server for those credentials.

    This article might provide some background info: http://blogs.msdn.com/b/brian_swan/archive/2010/02/10/sql-server-driver-for-php-understanding-windows-authentication.aspx

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    05 Januari 2012 17:26
  • Hi Brian

    I have double checked and i am using Anonymous Auth. I can log onto the server using DOMAIN\ryans and can log into the SQL Server Management Studio and run queries on th database under my DOMAIN\ryans login.

    Ive read that article before in the past. I gave me a better understanding but I still cannot connect.

    Anything else to suggest?

    Ryan

    06 Januari 2012 10:20
  • Thanks for everyones help but ive finally managed to sort it.

    I have created a new user, using SQL Auth and its working.

    Ryan

    06 Januari 2012 11:36