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?
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?