Question For Developers (long) - Windows Authentication

Factual Questions
1

BACKGROUND:

I’m currently handling duties of database administrator, alongside my other work. My programming days are far behind me (Fortran IV, anyone? :D), so I’m not up-to-date in that area.

We have a number of applications purchased from software companies for various aspects of our business. When a database back-end is required (usually) for the application, it goes on our central MS-SQL server. We’re pretty much a Microsoft shop, so all apps are Windows compatible and we use active directory accounts.

We are trying to move to all integrated Windows authentication, using Windows domain accounts for communication between servers (e.g., web server and database server for web applications) and between clients and servers (e.g., desktop app and database server for thick clients). Many vendors have no problems with this.

Other vendors, on the other hand, seem to believe this is tremendously difficult, nay, impossible to achieve. I’m in an ongoing tussle with several companies over this issue at the moment.

For instance, one company that said we could use a domain account between web server and db server, both in standard product documentation and in reply to specific questions we asked, now says we have to use a SQL server login, because the windows auth only works when web server and db server are on the same box because we’re using SQL 2005. :rolleyes: Another vendor has supposedly been working on a solution for two years, with no end in sight.

QUESTION FOR THE TEEMING MILLIONS:

Are we being unreasonable in our expectations? Is this really that difficult to incorporate into software? Or are these companies just giving us the run-around?

I come to you because I’m at the hair-pulling point with some of these guys. If we need to change expectations, I need some info to back up why. If not, I need some ammo to tackle the vendors with. I’m counting on you, O Teeming Millions. :stuck_out_tongue:

Any insight that database or application developers can give me, or even pointers to pertinent information available elsewhere, would be very much appreciated.

2

They’re giving you the run around.

I’m a programmer, and I’ve done extensive work with SQL Server and both standard Windows and web-based front ends, and I hear your pain. For some reason, a lot of shops seem to think that doing Windows authentication is a real pain in the ass. It really isn’t; assuming we’re talking straight Windows (not a mix of, say, Unix and Windows servers) and if they’re using a development environment that’s even remotely modern, it should be no problem to set up Windows authentication between their apps and SQL Server.

On the other hand, if they’ve got an antiquated code base with really old database calls, it may be more of a problem. But they should be able to handle it; Windows authentication is at least ten years old if I’m remembering right. Tell them if they need a consultant to tell them how to get their code up to date, I’m available :smiley:

If they’re web apps, you oughta be able to set up whatever authentication you want by changing the web.config connection strings and the domain pool that you’re running the app in. I can talk you through that if it’s your problem.

So as far as ammunition - I’d ask them specifically why they can’t use Windows Authentication. Is their code base incredibly old? Why isn’t the connection string in an easily editable file like an app.config or web.config file?

>>because the windows auth only works when web server and db server are on the same box because we’re using SQL 2005.

Flat out wrong. But you knew that.

3

As Athena ably points out, the vendors who are saying it’s too difficult are wrong. Microsoft even recommends SQL Server be run in Windows-only authentication if possible, using native application authentication only if it’s required for legacy applications. The only “same server” issue regarding SQL Server 2005 that I’m aware of is that developer editions by default only have the “shared memory” client protocol enabled, which limits it to connections from applications on the same machine. Of course, it takes about ten seconds to enable named pipes, TCP/IP and/or VIA protocols.

There are many best practices around separating your application servers from your main LANs, particularly for servers that handle outside traffic, but that’s beyond the scope of what you asked about.

4

Thanks very much for the confirmation, guys. After running into several of these in a row, I was beginning to doubt my sanity.

I think it’s mostly a combination of lazy and incompetent - they’ve done a poor job designing the interface, and now they don’t know how or don’t want to fix it. Or they’re trying to fit everyone’s setup (e.g., Windows / Linux, Oracle / SQL, Apache / IIS, etc.) and don’t want to write special code for the windows auth.

Really, I’m constantly amazed at the crappy software - and it’s not just occasional, it seems to be all of them. (Niche market applications, so the pool is pretty small.) The stories I could tell! Some of it’s damn near criminal, IMO. What’s even more astounding is that their other customers don’t seem to care - even the ones big enough to have a real IT dept that should know better.

Cerowyn - yes, we’re trying to move to Windows-only, but keep having this problem. Hell, I’ve got one vendor I’ve been hounding for a year now that hopes to maybe, possibly have their app compatible with SQL2005 by the end of the year. !!

Athena - thanks for the suggestions. I’m loving the idea of asking them those questions, then offering to find them a consultant. :smiley: So, is changing the web.config connections strings and domain pool something that can be done on the web server, regardless of the application? (Our last several web dudes have been internal promotions who learn on the job, so we’ve got a lack of experience there, especially on the admin side.)

Several of the apps are old-school ASP (no .NET) - is that particularly difficult to do connections in?

What about having a different account doing the db connections than is logging in? We have web apps that either allow anonymous users or do windows authentication on the user side, but we want them to use a single domain account between the servers. That’s another one that’s causing a lot of problems.

Thanks again, guys. You da best!

5

No problem!

If it’s a .NET application, and it’s written using standard .NET DB connections, the answer is yes.

The way to tell is to look where the application lives. There should be a file called web.config along with the .aspx pages and all the other source code for the app. There’s a section in there called <connectionStrings>, which contains all the various info that the app needs to connect to SQL Server.

The specific string will look something like:

<add name=“MyConnectionString” connectionString=“server=‘MySQLServer’;Initial Catalog=‘myDB’;Integrated Security=false;user=myUsername;password=myPassword;” providerName=“System.Data.SqlClient” />

If you want to change it to use Windows security, you need to change “Integrated Security” to TRUE. You can then remove the user and password entries, although if you leave them there they won’t cause any trouble.

The trick then is that the app will use whatever Windows account it’s running under to try to log in to SQL Server. If you’re using a newer version of IIS, the way you control what account an application is running under is through the Application Pool. Open up IIS, open “Web Sites” and look at your web site properties. On the Home Directory tab, the last thing on the page is Application Pool. Make a note of what it’s set at.

Now look for the “Application Pool” folder under IIS, and find the one that you just made a note of. Look at its properties - the last tab is Identity. It should look familiar to you if you’re used to Windows Authentication. Whatever user is set here is the user that your web site will use to log into SQL Server as, so you’ll need to set up SQL Server so that it has the right permissions for that user.

If you’re using an older version of IIS, there’s no “application pool” layer. There’s something right in the web site’s properties (if I remember right) where you tell it what user account to run as.

Does that make sense?

Unfortunately I’m not very familiar with old-school ASP. If you can get ZipperJJ’s attention, she might be able to answer that question.

Most web apps should be set up the way I described above, so it shouldn’t matter who’s logging in; the app pool account determines the SQL Server access.

6

Yep, it does, in a vaguely remembered way. I think we’re on IIS 6 now, but it’s been a couple years since I did any web stuff. I do remember a user setting in the website props. It’s close enough for me to figure out, at any rate.

That’s what I thought, I just didn’t know the specifics!

I think one of the problems is needing to connect as the domain account but still remember who the user is for application roles. I know they could hold onto the user info somewhere, but they don’t seem to realize that!

7

Well, if they used the .NET environment as it’s meant to be used, then they should get this functionality with no added coding. Of course, if they hacked together a bunch of non-standard code, then all bets are off. No matter how well the development environment is put together, nothing can protect us from bad coding!

8

Exactly.

On the bright side, I’ve spent much of my morning Doping legitimately! :stuck_out_tongue:

9

For Classic ASP you’ll need to root around in the Web site’s files and see where the developer wrote out the connection string or the connection string variables. The string is the same as what Athena wrote for ASP.NET, it’s just not usually encapsulated in an XML format.

Usually I use something like config.asp to hold that stuff, some people use connection.asp. It can be anything and be stored anywyere. But the values you set will be the same as what Athena outlined.

10

Hiya ZipperJJ, thanks!

Does classic ASP run into any problems with the login user vs database user that I mentioned before? Because I’ve had a couple of ASP people tell me that can’t be done.

11

What is your goal, here? Are you trying to protect, say, an Intranet by making people log in using their Windows login? For that you could just put NTFS perms on the folder that the ASP pages are in. People will be prompted for their Windows login when they get there.

Or is there a reason that each user would need a different set of db conn credentials each time they log in? How is the user base for your app set up already? If it’s programmed so that the users are stored in the database as a username and password, and they need to be manually added, changing the DB conn will not do you any good because the scripts are specifically looking in the database for credentials.

Or is your database actually set up so that when UserX logs in through Windows (imagine opening the DB in Query Analyzer) the available data and tables is very specific to UserX?

12

It depends on the app and who built it. :slight_smile: They’re mostly intranet, using Windows credentials to identify the individual user. We have a lot of tracking requirements due to the regulations we have to meet.

Simple stuff is not much problem, and the newer stuff we’ve built ourselves in asp.net is working as Athena says. I’m trying to understand enough to argue with vendors, some of which are older products built in classic asp.

One specific ASP app I’m thinking of is just a validation system, so that when the user hits an intranet web app, their credentials are checked against a separate database to see what level of access they are allowed in the original app.

So a user goes to our Widget web page. They could be a user level 1, 2, or 3 (each level can see a different subset of info), a user level 4 (who can see all the info), or a user level 5 (admin, who can add/drop content or users). First the user is verified in the validation database to see what their access level is, then the Widget app itself tracks actual usage by user (e.g., who did what).

The guy who built this (who was very inexperienced in web dev) got the code from somewhere. He insisted that since the validation app was passing the user credentials through to validate in the database, it could not use a domain account to attach to the database. I had to set up a standard SQL login. (I think the alternative was to add all the users as datareaders to the validation database.)

We’re not using NTFS perms because we’re pushing a lot of the access management out to admin-level users and don’t want to give them rights to modify network perms.

The other apps are purchased so I don’t have as many details, but I’ve been given similar reasoning by a couple of those vendors. (Like the one that automatically adds users to the database, and then gives them system admin perms to the whole database server if they have administrative rights in the application (e.g., to add/drop users or content “categories” or such).

I’m just trying to understand the concepts so I can argue with the vendors. (As a bonus, I can also get the inhouse app fixed if I can understand what’s needed; we’ve got a different web dev guy with no experience, but this one’s at least willing to listen to me on best DB practices.)

13

Honestly, all of my ASP programming is for the Web - the public Web. So I have never had to delve this far in to DB conns and Windows. I have never worked with Active Directory.

You might want to Google [asp “windows authentication” -net] to get some answers on classic ASP. Any time you Google “asp” anymore all of the answers are about .net, so taking out .net helps filter out the old stuff and get you back into the new stuff.

I am just speculating but I highly doubt that ASP can’t do what you’re looking to do. The barricade for the vendors might be that their app is not designed to do this at all and it’d take them some time to figure it out, and might break other stuff along the way. They may have programmed themselves into a corner.

If you really really can’t figure it out, or keep running into roadblocks, I can hook you up with someone that would be willing to work (not for free) to get you to where you need to be. One of those guys who won’t take “that’s impossible” for an answer :slight_smile: But definitely start with scouring the Web to see what you can find first, and keep throwing stuff at the vendors to see what they’ll do for you.

14

Despite what some folks above have said, there are some legitimate issues here.

The best practice pre-internet was to push the end-user credentials all the way into the database, and let SQL security decide who could see which procs, tables, views, columns, etc. That also let you put acess auditing at a level so close to the data that it couldn’t be bypassed. Those credentials could be Windows creds or SQL internal creds, but either way they get al the way to the database.

That started to fall apart when people built extranet apps with users who don’t have domain credentials. It also fails to scale for huge active user counts because of connection pooling limitations.

So the newer model is called “trusted subsystem” where the front end app (web or otherwise) does security checking of the user credentials & then always connects to the database as a single pre-configured user with the highest SQL authorization needed by anyone using the app. And now the app has to be responsible for any access auditting, since the database doesn’t know who the real end-user is.

Finally, if we are talking about a web-based app under IIS, you have the issue of impersonation. The app can really be executing machine instructions as either a single configured server user or as the end user who must have credentials on the box or, more normally, the domain.

There are plusses & minuses to both ways, but it’s not likely any complex app can be switched from one to the other and still work correctly.
There are also issues with using credentials beyond the web server. If the server is configured for NTLM (so-called Windows integrated) auth, and impersonation is on, the app runs as the end-user. That’s fine, but the credentials on the server are not passable to any back end servers; they’re local box use only. The common workaround for that is to use basic auth so the web server gets the plain text username & pw. Now the app can use those plain text strings to construct a portable credential set to pass along to the backend server. But then that pretty much requires ssl to secure the password in transit, and … .

See http://support.microsoft.com/kb/891031/en-us for more, but understand the challenge is really a feature of Windows’ credentials facility, not of ASP.Net, and any client-server app will have to deal with this issue.

Now Kerberos is intended to simplify all this. BUT … kerberos doesn’t work over the internet. Only over a LAN/WAN/VPN.

As you can see, this gets deep quickly. The folks who sold you the app have their chosen compromise on all the above. They may even have designed in the flexiblity to do things more than one way. But for any complex enterprise app, they aren’t going to be able to accomodate every possible combination of settings & technonogies. It could have been buit for whatever combo you want; but that doesn’t mean it was built that way.
Bottom line: For some apps which were well-designed at the time, switching from one flavor of auth to another can be quite traumatic. For badly designed apps, plan to spend a boatload of money.

For those well-designed apps created in the last 2 years or so, switching will be easy. Until 3 years from now when some other new & unforeseeable technology comes out that the now-legacy app won’t be compatible with.

15

Quick Q - Active Directory Domains? THen why not push authentication up a couple levels to the domain level like it should be in best practice? User logs in once, has a centrally managed permissions store, which then authorizes and authenticates him across the board.

If it is, use MIIS (Microsoft Identity Integration Server) or similar for single sign on. Info here. It’s what it’s designed for, and it means you do you signing-in at the domain level rather than at the app level, which also means that rather than changing dozens of apps for security changes (i.e. password expiry) you just change 1 AD object per user.