none
How to add SQL Credential Role to a application. RRS feed

  • Question

  • 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. 


    • Edited by Airizzo Wednesday, February 26, 2020 8:15 PM
    Wednesday, February 26, 2020 8:12 PM

All replies

  • Have you looked at SMO ?

    https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo?view=sql-smo-140.17283.0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, February 26, 2020 8:36 PM
    Moderator
  • hello friend

    This the application role.But for sql ser role bello is the query

    SELECT DP1.name AS DatabaseRoleName,   
       isnull (DP2.name, 'No members') AS DatabaseUserName   
     FROM sys.database_role_members AS DRM  
     RIGHT OUTER JOIN sys.database_principals AS DP1  
       ON DRM.role_principal_id = DP1.principal_id  
     LEFT OUTER JOIN sys.database_principals AS DP2  
       ON DRM.member_principal_id = DP2.principal_id  
    WHERE DP1.type = 'R'
    ORDER BY DP1.name;  

    Friday, February 28, 2020 3:03 PM
  • hello friend

    This the application role.But for sql ser role bello is the query

    SELECT DP1.name AS DatabaseRoleName,   
       isnull (DP2.name, 'No members') AS DatabaseUserName   
     FROM sys.database_role_members AS DRM  
     RIGHT OUTER JOIN sys.database_principals AS DP1  
       ON DRM.role_principal_id = DP1.principal_id  
     LEFT OUTER JOIN sys.database_principals AS DP2  
       ON DRM.member_principal_id = DP2.principal_id  
    WHERE DP1.type = 'R'
    ORDER BY DP1.name;  

    Not sure why you are using a SELECT when the task is to add.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, February 28, 2020 3:15 PM
    Moderator