3

I am having trouble finding documentation on how to impersonate a user and open a SqlConnection as that user.

Background:
DBAs have provided an Active Directory account that has access to a database. When using the connection string:

"Server=server\instance;Initial Catalog=mtdatabase; User ID=domain\user; Password=mypassword;";

I get an error that the user is not allowed. When using Integrated Security, it ignores the specified Username and Password, and uses the currently logged in user (me when I am debugging locally).

How can I impersonate the user with the provided information in my code to ensure that it is used to open up the connection?

Currently, I am using the following code, which is failing when trying to open the connection:

[Route("api/[controller]")]
public class HomeController : Controller
{
    // GET: /<controller>/
    [HttpGet]
    public IEnumerable<string> Index()
    {
        string connString = "Server=server\\instance;Initial Catalog=mtdatabase; User ID=domain\\user; Password=mypassword;";

        using (var conn = new SqlConnection(connString))
        {
            conn.Open();
            conn.Close();
        }
        return new string[] { "value1", "value2" };
    }
}
blgrnboy
  • 4,877
  • 10
  • 43
  • 94
  • If I understand your scenario, you need to take the credential and connect to SQL Server, I think you need to set windows authentication in your site, have you applied this setting ? – H. Herzl Dec 08 '16 at 03:16
  • No I have not. A couple of question about that. Does using Windows Authentication still allow the site to be cross platform? Is it necessary to have the entire site use Windows Authentication, when all I need is for Sql to use it? – blgrnboy Dec 08 '16 at 03:39
  • Windows authentication it's exclusive of Windows platforms but I think if you publish on windows you can use it, have you read this question http://stackoverflow.com/questions/37694211/windows-authentication-with-asp-net-core ? – H. Herzl Dec 08 '16 at 04:08
  • Prior to .NET Core, all you'd have to do is disable Anonymous Authentication, enable Windows Authentication, and enable Impersonation (either via the web.config of the project or if deploying to IIS you can also do it in the Authentication settings of the site.) .NET Core scrapped Impersonation so this is no longer possible. They do offer a somewhat legacy solution in how to still do it but it isn't really recommended, especially for heavy units of work. Impersonation at end: https://docs.microsoft.com/en-us/aspnet/core/security/authentication/windowsauth?view=aspnetcore-3.1&tabs=visual-studio – J.D. Oct 29 '20 at 13:12
  • Did you ever find the solution to this? I have the same problem. – EJoshuaS - Stand with Ukraine Nov 09 '21 at 03:16

0 Answers0