permissions with sql server

Posted on 2007-11-19
Last Modified: 2010-03-19
i want my web ap to be open access, but the actual authentication done on sql server.  do my first screen is a login screen, but when i choose windows authentication is says login failed for user ''.  what do i need to chage in iis in order for it to pick up the actual windows user.  how do i give my website full access for viewing?  this is located on an intranet.  all users can view just limited on the data they can view.  
Question by:Fraser_Admin
  • 4
  • 3
LVL 16

Expert Comment

ID: 20315167
LVL 42

Expert Comment

ID: 20315218
I don't understand your configuration.  Normally, web apps do not see an SQL Server login screen.   Can you please explain, when/where do you get the login screen.  Is it an SQL Server login screen?  Is this a static page or is it ASP?

Author Comment

ID: 20315325
i created the screen to allow people to log in either using windows auth, or to use sql server.  so when they choose windows auth, i need to use the userid and pwd that they are currently logged onto their box with.
LVL 42

Expert Comment

ID: 20315585
>so when they choose windows auth, i need to use the userid and pwd that they are currently logged onto their box with.

That's not how windows auth works.  Windows auth assumes the user is already authenticated by windows and simply passes a token representing that windows account to the backend database.  The database "trusts" that token and grants any permissions that are assigned to it.  Neither a userid, nor a password is actually passed.  

When ASP is involved, it's more complicated because the database connection originates from the IIS account, not from the client account where the user has logged in.  To make this seamless, IIS must be configured to impersonate the windows client so that it passes the client token rather than it's own.

Needless to say, the setup takes planning and effort.  I encourage you to check out this resource which gives a thourough explanation:

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.


Author Comment

ID: 20319269
i have anonymous unchecked and windows authentication checked in iis.

i have added the 2 tags to web.config for the ap.

when i'm on the test server it works fine.  when i try from anywhere else i get...
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(18456)

Author Comment

ID: 20319372
i have also turned deleteation on, on my test server.  any other ideas here?
LVL 42

Accepted Solution

dqmq earned 500 total points
ID: 20322250
I have no other suggestions here, except, that it appears IIS is attempting to use anyonymous despite your claim that it is suppressed (unchecked).

Author Comment

ID: 20322327
i think it is because i have the sql server on one machine and IIS on the other machine.  therefore it is doing a two-hop and not authenticating.  do you have any experience with sql on one machine and IIS on another?

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Lync server 2013 Backup Service Error ID 4049 – After File Share Migration
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now