• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

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)?
0
Ben Santiardo
Asked:
Ben Santiardo
  • 6
  • 4
  • 3
1 Solution
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
Carlos VillegasFull 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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now