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)?
Ben SantiardoAsked:
Who is Participating?
 
Carlos VillegasConnect With a Mentor Full Stack .NET DeveloperCommented:
Ok, buddy you need to do this:
1. In the machina that will run your service, create a new local user account and give to it the rights required to run locally your application, dont leave a blank password.
2. In the service properties, tab Log On, set that account, with the correct password.
3. In the remote machine, create the same windows account (same name, password) and on the sql server instance create a new login related to that account, set the corrects permissions to be able to create a DB in your sql server instance.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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)
0
 
Ben SantiardoAuthor Commented:
@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...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
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.
0
 
Ben SantiardoAuthor Commented:
@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?
0
 
GSGDBACommented:
Change the access tpe to NT AUTHORITY\SYSTEM.
0
 
GSGDBACommented:
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.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
also, are you in a domain?
0
 
Ben SantiardoAuthor Commented:
@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!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Well then I think that you have all under control, right?
0
 
GSGDBACommented:
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.
0
 
Ben SantiardoAuthor Commented:
@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!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
We are here to help buddy! glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.