0

Below code is working fine when I am coding. I publish code in IIS and then connection is failing.

I know, When I am coding is taking my window login as credentials and In IIS it is taking AppPool Identity. How can I pass my windows login credentials to connectionstring?

Public Shared Function DbCollection(connectionString As String) As DatabaseCollection
   Dim server As New Microsoft.SqlServer.Management.Smo.Server(connectionString)
   Return server.Databases
End Function

Error:

   Login failed for user 'Domain\Computername$'. 
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SqlClient.SqlException: Login failed for user
 'Domain\Computername$'.
Improve this question
3
  • Why would you want to? This kind of authentication should be made with a service account specific to this purpose. Is there a reason you want (or need) to use your windows account. I'm assuming you don't really mean YOUR credentials, but the credentials of the authenticated user. Commented Feb 18, 2013 at 16:25
  • Because this application using my one user and it will be deployed on his machine only. Commented Feb 18, 2013 at 16:29
  • That's still not the best reason to do this. I would still recommend using a service account for this kind of access. Commented Feb 18, 2013 at 16:32

2 Answers 2

Reset to default
1

Based on error it is likely your SQL is not on the same box as IIS... Here are different possible options depending on configuration:

  • If you are not using SQL authentication and your SQL on the same box: You should be able to get the user credentials with HttpContext.CurrentUser and impersonating with those credentials, but it depends a lot on your system setup. Likely your already impersonating (default configuration in ASP.Net), but if your server is on different box than IIS than regular user's credential will not be able to float to other box (search term "NTLM one hop").
  • your database on a different server and you are already using Kerberos authentication in your organization: I think you have to be authenticating with Kerberos to impersonate on another server. Note that if such authentication is not configured/allowed it is unlikely to get turned on just for your case.
  • Database on another server using Windows authentication - you can run all SQL queries under particular user account (or RevertToSelf with whole bunch of interop to run under process' account).
  • Database anywhere and you can use SQL authentication - easiest approach as long as it is OK in your organization. Consider encrypting the connection string in config file if using this method.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+0: While in general idea of impersonating particular user is good, normally there is no need to impersonate "current user" as default for ASP.Net is "Windows auth + impersonate". Using Kerberos is likely impossible (unless it is already setup), impersonating a particular account (or reverting with RevertToSelf to process credentials) maybe easier. Using SQL authentication with encrypted password stored in Web.Config is likely easiest approach.
I wasn't suggesting that switching his authentication to Kerberos was a solution, I was saying that if he already uses Kerberos then he can do that. I also give the poster the benefit of the doubt that maybe he really does need to use the credentials of the user, and using a service account is not acceptable. After seeing the OP's comment, I might have a different answer, but at the time I answered the question that was presented.
+1 - I did misread your statement about Kerberos. See if you ok with my edit - tried to make clear that there are 2 different cases in your original post (and took liberty to add my options too).
+1 Alexei - Yes, much clearer than I had written it, and more concise. If only I could pass the points on to you for the answer!
0

I'd add to @smoore's post that a lot depends on the connection string and the configuration of sql server of allowing incoming connections. It might be set to only allow sql users, not windows ones which is looks like from the problem description. Can you give details of the connection string (anonymize/delete the user/password before pasting!) and what authentication mode is allowed by the sql server instance?

Improve this answer

4 Comments

connection string is only 'sql server name'
so it doesn't have "Integrated Security=true" or a "Username=;Password=;" tags?
Microsoft.SqlServer.Management.Smo.Server($strServer) will take of rest of the thing
ah, then it would seem that the user account that is trying to connect to the sql server instance is the current windows account for the process? If running in your dev environment this is your logged in user. If you're running in IIS then this is the asp.net user account unless otherwise specified. By default, this would not be set up in SQL Server. In SQL Server's 'security' section, you'd need to create a SQL user account and configure it to allow remote connections. Only then would it be able to connect. If so, they really restrict the type of access to the DB

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.