How to add SQL Credential Role to a application.

[Airizzo]

I already have a program, it uses logon USER Dllimport to verify against windows that you are who you say you are. I want to add SQL server database Role credential with a stored procedure to it and have NO idea how to do that. I would like help if possible.


So far I have

public partial class Login : System.Web.UI.Page
{

[DllImport("ADVAPI32.dll", EntryPoint =
"LogonUserW", SetLastError = true, CharSet = CharSet.Auto)]
public static extern bool LogonUser
(
string lpszUsername, string lpszDomain,
string lpszPassword, int dwLogonType, int dwLogonProvider,
ref IntPtr phToken
);


public static string GetDomainName(string usernameDomain)
{
if (string.IsNullOrEmpty(usernameDomain))
{
throw (new ArgumentException("Argument can't be null.", "usernameDomain"));
}
if (usernameDomain.Contains("\\"))
{
int index = usernameDomain.IndexOf("\\");
return usernameDomain.Substring(0, index);
}
else if (usernameDomain.Contains("@"))
{
int index = usernameDomain.IndexOf("@");
return usernameDomain.Substring(index + 1);
}
else
{
return "";
}
}

public static string GetUsername(string usernameDomain)
{
if (string.IsNullOrEmpty(usernameDomain))
{
throw (new ArgumentException("Argument can't be null.", "usernameDomain"));
}
if (usernameDomain.Contains("\\"))
{
int index = usernameDomain.IndexOf("\\");
return usernameDomain.Substring(index + 1);
}
else if (usernameDomain.Contains("@"))
{
int index = usernameDomain.IndexOf("@");
return usernameDomain.Substring(0, index);
}
else
{
return usernameDomain;
}
}


protected void btnLogin_Click(object sender, EventArgs e)
{
string domainName = GetDomainName(txtUserName.Text);

string userName = GetUsername(txtUserName.Text);

IntPtr token = IntPtr.Zero;
FormsAuthentication.Initialize();
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial catalog=web;");
SqlCommand cmd = conn.CreateCommand();


bool result = LogonUser(userName, domainName, txtPassword.Text, 2, 0, ref token);
if (result)
{

if (string.IsNullOrEmpty(Request.QueryString["ReturnUrl"]))
{
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, false);
} else
{
FormsAuthentication.SetAuthCookie(txtUserName.Text, false);
Response.Redirect("default.aspx");
}
}
else
{

Response.Write("Invalid username or password.");
}
}
}
}


Now I need to combine that, with the ability to query Sql and get a role for the user from our database using a Stored Procedure. I have NO idea how to do that. I started looking up ways, and added some Sql stuff, but thought I would post and see if I was on the right track and find out exactly what else to add and especially where! If you need SQL code, just let me know and I can post that as well.

Continue reading...