If you can connect to SQL Server and access the user database using Query Analyzer from the client machine logged in as the same windows user who executes the application, then you are right, the problem is surrounding the way the application is connecting to SQL Server rather than any general SQL Server authorization set up issue.
Forgive me if I am stating the obvious but it must be cleared up. For the application to use Windows Authentication it must have an option to choose between Windows Authentication or SQL Server Authentication. You do NOT type in the username and password of your Windows User Account into the application to connect to SQL Server using Windows Authentication. You only type in a username and password into an application to use SQL Server Authentication, the Windows Authentication information is based on the user who you are currently logged into Windows as.
For the application to connect to using SQL Server using Windows Authentication it will have to send a different connection string than what it sends when connecting using SQL Server Authentication. Check with your developer to ensure that the correct connection string is being set within the application.
I would also run SQL Server Profiler and trace the "SecurityAudit:Login" and "SecurityAudit:Login Failed" events. From this trace you can see 1) If the application is even trying to connect to SQL Server when the application is using Windows Authentication and 2) If what authorization credentials are being sent to SQL Server.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2006