ConnectionFailureException thrown in XP when using SMO with Windows Authentication

Question

I'm trying to use SMO in a ASP.NET web project to get a list of the server's Databases. The method I'm using seems to work fine on a Windows 7 machine, but the second I install it on an XP machine, I get a ConnectionFailureException. The code I'm using to establish the connection is:

        ServerConnection connection = new ServerConnection(serverName);
        Server serverConnection = new Server(connection);
        string[] databases;
        try
        {
            databases = new string[serverConnection.Databases.Count];
        }
        catch { databases = new string[0]; }

On the Windows 7 machine, I get an empty array of length however many databases there are, which I then add the database names to in a foreach loop, but in Windows XP, it fails in the try block, and I get:

ConnectionFailureException: Failed to connect to server localhost. 
-> Login failed for user 'ComputerName\\ASPNET'.

I'm guessing this is some kind of permissions problem with the ASPNET user, but I can't seem to find anything that's solved the problem. In IIS, I unselected Anonymous Access and selected Integrated Windows Authentication, and set

<authentication mode="Windows" />

in the web.config.

Anyone have any suggestions/sage-like advice to share?

So you got IIS on both W7 and XP. What user does W7 use to connect to SQL? Can't you impersonate XP's IIS to use the same one? — Kodak, Aug 20 '12 at 21:00
The W7 machine is using "IIS APPPOOL\\ASP.NET v4.0" as a user, and XP is using "ComputerName\\ASPNET" user. I don't think that IIS on XP supports app pools, so I don't think I can just use the same user. — Devo, Aug 21 '12 at 13:28
If I manually add the ComputerName\\ASPNET user to SQL Server Management studio, it works, but this isn't exactly an ideal solution. — Devo, Aug 21 '12 at 13:41
no matter what user is trying to connect to SQL machine in needs to be allowed to connect = exist belong server logins; one must always be aware how application is recognized in database - I always give application as less permissions in db as possible; if you got domain there best would be to run both IISes on the same domain account; if you do not have a domain then maybe a single sql login would be better for both environments — Kodak, Aug 21 '12 at 17:10
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/15616/discussion-between-kodak-and-devo) — Kodak, Aug 21 '12 at 17:12

score 1 · Accepted Answer · answered Aug 21 '12 at 20:22

Do you want web application to impersonate a user to connect to SQL? When you use SQL Management Studio you are connecting to SQL directly; when you are doing it from web application then you are calling it and IIS calls yours SQL. Now the question is which login IIS uses when accessing SQL - whether it impersonates you under W7 and does not doing it under XP?

For sure XP does not impersonates you and uses 'ComputerName\ASPNET' as you shown in the error message. IIS6 ASPNET impersonation setting are described here and IIS7 here. By default in both IISes impersonation is turned off but I am not sure what is your current configuration on W7. Maybe you should turn it on under IIS6?

ConnectionFailureException thrown in XP when using SMO with Windows Authentication

1 Answers1