I’ve said it before: database folk have to know everything. Today was no exception for me. I’ll walk you step by step through the steps I took to get the job done, in hopes that something will be useful to you now or later. Note: I am not an IIS administrator! I’m a little surprised I remembered as much as I did…
The Setup
I got a ticket today to give a user read/write permissions for a database that I haven’t really touched. (I’m still very new here.) Sure, no problem. “By the way”, I asked. “What are you doing in this database?”
As it turns out, the user needed to log into an internal website that used this SQL database as a back end. It quickly became apparent that it wasn’t database credentials the users needed; a quick Profiler trace showed that the website wasn’t requesting credentials from SQL Server. The user needed a working login through the website. Let the sleuthing begin!
Step 1: Where is the website hosted?
As in many shops, at my office a lot of the systems are old, near retirement, and obscure, and the people who know about them have themselves retired or otherwise gone away. So we’re on our own today. I need to get at the server that’s hosting the website, but which one is it? I tried this in a command line:
ping Eggy.OurWebsiteName.com
and I got the IP address. But it wasn’t the right one! Oh yes, you can assign a different IP to a sub-site (like Eggy.OurWebsiteName.com). This gave me the IP I wanted:
ping OurWebsiteName.com
From there, I was able to open a remote desktop connection (Run > mstsc) to the server and log in, and I found that it was IntranetServer1. Step 1 accomplished!
Step 2: How is the website authenticating?
I knew from running a quick Profiler trace that the website wasn’t requesting credentials from SQL Server. But what was going on? I:
- opened up Administrative Tools > Internet Information Services (IIS) Manager from the start menu
- expanded the local computer and then Websites
- quickly found my Eggy entry
- right-clicked on Eggy and selected Properties
- clicked on Directory Security
- clicked the Edit button under “Authentication and Access control”
Hey, lookit! This website uses Integrated Windows authentication, and the “Enable anonymous access” checkbox is empty! Okay, so we’re only allowing users to authenticate via Windows…that makes sense for corporate intranet.
Step 3: How can I grant permissions for a user?
This was the tricky part for me, and I won’t hold you in suspense. I pinged Sean, who got me the answer: For a website with Windows authentication (and no anonymous auth), the user needs permissions to the folder in which the website resides(in this case, L:\websites\Eggy\ on the server). The people that already had permissions to that folder via a group could log in, but if you didn’t belong to the right group (or weren’t individually granted permissions), you couldn’t login. Voila!
I created an “Intranet user” group with all domain users (in our case, this was appropriate as all domain users should have access to Eggy), and assigned that group rights to the folder. While you can take care of such things individually, it’s really much more manageable through groups.
Step 4: Crack open a cold soda and some warm popcorn
We’ve done it, and we are heroes! Database guys….we really DO know everything! Sort of…
Happy days,
Jen McCown
Nice one, thanks a lot… this kind of posts really make my days 🙂