Link to home
Start Free TrialLog in
Avatar of Ben Santiardo
Ben SantiardoFlag for United States of America

asked on

Integrated Security On Connection String Causes Login Failed Error...

Using VB.Net 2.0 (VS2005), I compile a connection string that set "Integrated Security = True". I then executed a SQL Command using "ExecuteNonQuery()" to "CREATE DATABSE..." which worked fine on my Windows XP machiene.

I have recently upgraded to VB.Net 4.0 (VS2010) and a new computer (Windows 7). I run the same exact code and I now fail on the "ExecuteNonQuery()" with an error stating "Login failed for user <Domain>\<ComputerName>$"

My confusion is two-fold. One, why is ADO.Net passing over my "Computer Name" rather then my "User Name" for Integrated Security.  And two, why does it behave perfectly fine on my old computer (WinXP / VS2005) and fail on my new machiene (Win7 / VS2010)?
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image

Hi
1. Be sure that your account have the sysadmin role in your sql server instance.
2. If your account is already a sysadmin, run your program with administrative privileges (right click Run as administrator)
Avatar of Ben Santiardo

ASKER

@yv989c:

1) As I stated in my original post, this code works perfectly find on my original WindowsXP machiene.  As such, that shows that the problem has nothing to do with account permissions.

2) I can't "Run As Administrator" because this is code that I am running at the moment in degub mode.  Although I do not think that "Run As Adminstrator" would have any effect on this code.  The problem as I stated, seems to be VS2010 (ADO.Net 4) is passing my ComputerName rather than my UserName when I attempt access using Integrated Security.  At least that what it appears to be doing...
Please start your VS with right click "run as administrator", and debug your program, see if that fix your problem. Be aware of this, you are doing a operation that requires administrative privileged in your sql server instance. In Win7 that has change drastically. Just try what I say and if it work we can find a better solution.
@yv989c:

I was typing my response to you, when something dawned on me. The code in question, is running as a SERVICE using the "Local System" account on the local machiene accessing the SQL Server on a remote machiene.  PREVIOUSLY, the code ran as a simple Windows Forms application which of course ran as the currently logged in user.

I am going to take a leap here and say this is probably why "Integrated Security" is not working as I expected it would?  I would hazard a guess that the SERVICE is using the "Local System" account when accessing the SQL Server as it's credentials, which I assume would be the reason why the ComputerName is being used rather then my UserName.

If so...how do I tell the SQLCommand object to impersonate the Credentials of the user that initiated the code?
Avatar of GSGDBA
GSGDBA

Change the access tpe to NT AUTHORITY\SYSTEM.
ASKER CERTIFIED SOLUTION
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
and also check whether the old compter is part of the same domain as new machine.
check the domain\user has access to sql server.
also, are you in a domain?
@yv989c:

Since right now, the SERVICE is installed on my Local Machiene for testing purposes, I think I could just tell the SERVICE to run as my Local User Account, which is already set up on the SQL Server with proper rights.  That in essence will "manually" setup impersonation.  Once I have the code tested, the SERVICE will ultimately be running on the same machiene as the SQL Server, so I should have a problem at that point.  The problem I was having now was simply because the SERVICE was running remotely. Thank you for this!!!  :-)

And yes, I am on a domain.


@GSGDBA:
Just curious, how would I change the SERVICE logOn to NT AUTHORITY\SYSTEM?  And what exactly would doing that get me?  I ask only because I like to weigh all my options.  Thanks!
Well then I think that you have all under control, right?
run -> key in "services.msc "
Now you would see services mgmt console.
Select the service you use and r.click to see the properties. Now you will be able to change the authentication type.
@GSGDBA:

From the Services Manager, I have the option of using "Local System Account" (Whcih was causing my problems) or a "Specific User Account".  There is no NT AUTHORITY\SYSTEM option.

@yv989c:

Yes, I think I do now.  Thank you for your help, it's much appreciated!
We are here to help buddy! glad to help.