asp.net SQL Server Authentication using Windows Authentication

Question

I am currently working on a ASP.NET admin dashboard. The app pool for this IIS website is running with a domain user, which is used to connect to a SQL Server that holds various data for the webpage.

Now, on this website I want to connect to many different SQL Servers using either SQL Server Authentication (if the SQL Server is in a different domain) or Windows Authentication (a separate Windows Account for each SQL Server).

In SQL Server Reporting Services we can create data sources and define how to connect to the databases - basically I want to do the same, but within my ASP.NET code. The authentication information will be stored in a SQL Table (encrypted) accessed by the app pool user.

And no - I can't use SQL Server Reporting Services =)

Additionally, I need to connect to SQL Servers starting 2005 up to 2017 or any upcoming new version. I will have 5 - 100 Servers that I need to query (depending on the scenario).

How can I do that? Did anybody implement something like this? Do you have any tutorials or references?

Thanks in advance

All you need to do is build a connection string programmatically using the info from your table. The {SqlConnectionStringBuilder class](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder) can facilitate that. — Dan Guzman, Mar 25 '18 at 13:34
Would this work on a ASP.NET webpage? [link](https://stackoverflow.com/questions/125341/how-do-you-do-impersonation-in-net/7250145#7250145) — Antonio Turkovic, Mar 25 '18 at 15:13
The class should work with any .NET application. It's just building a string. Of course, you could just build the string yourself too. — Dan Guzman, Mar 25 '18 at 15:46
@Dan Guzman - Thanks, but I don't mean building the string - that should be pretty easy - more difficult is how to connect to a database using a different windows account, which needs some impersonation - I posted a link in the previous comment. (That's what I meant with "Would that work on a ASP.NET webpage") Thanks for you support and advice =) — Antonio Turkovic, Mar 25 '18 at 17:41
I missed that you wanted to impersonate different Windows account for each server. If those are in the same domain, why not use the same app domain account for all? — Dan Guzman, Mar 25 '18 at 17:52
That would be the easiest way, I know - but it's one major security requirement for this solution to use different Windows accounts for different SQL Servers. And it is preferred using Windows authentication before using SQL Authentication. — Antonio Turkovic, Mar 26 '18 at 05:14

score 0 · Answer 1 · answered Mar 26 '18 at 11:39

In order to that, I need to use an UserImpersonation Class provided by the "UserImpersonation" nuget package.

Here is a code snippet with which I've test the three authentication methods:

App Pool Identity
SQL Server Authentication

Windows Authentication with Impersonation

public string TestLogin(string UserName, string Password, string Auth, string Domain)
{
string thisLogin = "";
//App Pool Identity
if (Auth.Equals("local"))
{
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
    {
        SqlCommand cmd = new SqlCommand("SELECT SUSER_SNAME()", cn);
        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            thisLogin = rdr[0].ToString();
        }
        cn.Close();

    }
}

if(Auth.Equals("SQL"))
{
    using (SqlConnection cn = new SqlConnection("Data Source = .\\D01; Initial Catalog = master; Integrated Security = false; User ID = " + UserName + "; Password=" + Password + ";"))
    {
        SqlCommand cmd = new SqlCommand("SELECT SUSER_SNAME()", cn);
        cn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read())
        {
            thisLogin = rdr[0].ToString();
        }
        cn.Close();
    }
}

if(Auth.Equals("Remote"))
{
    string login = UserName;
    string domain = Domain;
    string password = Password;

    using (UserImpersonation user = new UserImpersonation(login, domain, password))
    {
        if (user.ImpersonateValidUser())
        {
            using (SqlConnection cn = new SqlConnection("Data Source = .\\D01; Initial Catalog = master; Integrated Security = SSPI;"))
            {
                SqlCommand cmd = new SqlCommand("SELECT SUSER_SNAME()", cn);
                cn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    thisLogin = rdr[0].ToString();
                }
                cn.Close();
            }
        }
    }
}

if(thisLogin.Equals(""))
{
    thisLogin = "User failed";
}
return thisLogin;
}

It's not perfect - but it should work.

asp.net SQL Server Authentication using Windows Authentication

1 Answers1