3

I need to connect to Sql Server 2008 using windows authentication from PHP code by passing the username and password like we do when we connect to Sql Server with SQL Authentication. I need to authenticate the user based on Windows Authentication's User/Password .

When I was using SQL Authentication I was able to connect by creating a System DSN named DBconnect in the Data Source (ODBC) configuration tool. Then I could successfully verify the login ID with SQL Server Authentication and connect to the database using Login ID and Password entered by a user with this php code:

$con = odbc_connect("DBconnect", "sql_user", "sql_password");

Now I need to do the same thing when SQL Server is using Windows Authentication. I want to create a System DSN named DBConnect_NT in Data Source (ODBC) Configuration tool and set it to verify the authenticity of the login ID with Windows Authentication using the network login ID.

I have this code:

$con = odbc_connect("DBconnect_NT", "windows_user", "windows_pass");

But it doesn't work. I get this error:

Error in query preparation/execution. 28000

I changed some settings in the Authentication section of IIS7 Manager

Windows Authentication = Enabled
Anonymous Authentication = Disabled

Now I can use this code:

$con = odbc_connect("DBconnect_NT", "", "");

and I see a popup prompting me to login. As I type username and password, It connects and hence allows me to do other queries using same $con connection object.

Unfortunately, I'm not able to authenticate the user for the web site based on this approach. When I type wrong username and password to the popup it gives no response (the popup comes again). If I cancel the popup, it will show this 401 error:

401 - Unauthorized: Access is denied due to invalid credentials.

Am I using correct way to connect to SQL Server via Windows authentication? Is there some other possible way to connect SQL Server by passing the Windows Authentication username and password dynamically?

I have also tried using LDAP to authenticate against Active directory, but its not working.

I'm using SQL SERVER 2008,IIS7 and PHP5.3 in an Amazon EC2 machine.

This is for a flex application I'm building using http://example.net/Webservice/connect.php. Flex dynamically passes the username/password to this popup from a form and hence connects. But when wrong username/password are supplied the popup is difficult to handle. It comes back continuously and the flex application hangs .

If it is possible to receive an error code when the wrong username/password are entered in the popup it may solve the issue. Some other method to connect SQL Server using Windows Authentication and pass the username/password directly would also solve my problem.

Improve this question
2
  • I've never been able to connect to SQL Server via windows authentication by passing in the windows user/password. You need to be pre authenticated on active directory as that user then login without user/pwd. Are you connecting from a windows platform or other? You could also try connecting as a SQL User then use EXECUTE AS to change to a different user: msdn.microsoft.com/en-us/library/ms181362.aspx Commented Sep 6, 2015 at 3:09
  • If I understand correctly, windows authentication works fine, its just that when an incorrect login is entered you can't handle that in your web app? So basically the SQL Server connection is not an issue, it's getting your particular web development platform to catch and manage an incorrect login Commented Dec 13, 2016 at 23:23

2 Answers 2

Reset to default
1

When you use Windows Authentication, Sql Server does not see your username or password at all. Stop and read that sentence again, because it's very important to understand this.

For Windows Authentication to work, you must first authenticate directly with an Active Directory Domain Controller (DC). When you successfully log in, the DC will issue a security token for your session. It's this token you provide for Sql Server Windows Authentication to work, and not your username or password. Sql Server only verifies the token.

This is why the LDAP methods do not work. LDAP only provides a "yes/no" response from Active Directory. LDAP does not provide the security token you need. Think of it as the Windows equivalent to using Certificate Authentication to a linux server. Moreover, this token is associated with the process making the connection. It's not a property you can just set on a connection object. This is a security feature to make it difficult to steal or hijack tokens.

That creates problems in a web site, where you need a way to store and manage the token for each user of the site, and attach it to your web server process. IIS provides a mechanism for this called Impersonation. It was built originally for ASP.Net, but it seems FastCGI does support it via the fastcgi.impersonate setting in the php.ini file. But note I've never personally tried to use this, so I can't speak to how well it works.

You also need a way to connect to the DB on public pages pre-authentication, and a way to connect the authentication to the Active Directory account, which will limit authentication mechanisms available to you. Not being a regular PHP user myself, and not knowing how your AD environment relates to your EC2 web server instance, this is as far as I can take you.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

To use windows authentication for sql server, you don't specify user name and password. Instead, you change the connection string for integrated security, as in:

Connection string = "Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;"

or the variant for trusted connections:

Connection string = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;"

Or if you are using an ODBC DSN, skip user names and password in a similar way.

On top of that, you need to be understand the Windows user connection to the db in this situation is the IIS account running your site. The Windows Authentication stuff you did would be a local intranet: all your users are part of a Windows domain, or have a trust with it.

If your search on here for IIS PHP and SQL server, you'll get some info that might be useful.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.