Windows impersonate [SQL, ASP.NET, C#]

Question

I try use windows impersonate in asmx web service to read sql database.

I make new account in windows.
Set permission to full control on database file ORLDatabase.mdf.

Now I call method GetDataSet, but it finish on client side with this error:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file D:\work\WebService\App_Data\ORLDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I check windows impersonate in code with WindowsIdentity.GetCurrent(), the current identity is good.The Account have full control on databse file,but it finisch with error. Can somebody help me, I dont't work with sql. I try first google, but don't find solution which solve my problem. Thank

public class Service : System.Web.Services.WebService
{
    public TicketHeader Ticket;
    public DataSet ds;

    private string machine = "pcName";
    public string userName = "********";
    public string password = "*********";
    public IntPtr token; 
    public WindowsImpersonationContext impersonationContext;

    [DllImport(@"D:\Windows\System32\advapi32.dll")]
    public static extern bool LogonUser
    (string lpszUserName, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, out int phToken);
    public void Login()
    {
        int returnedToken;
        if (LogonUser(userName, machine, password, 3, 0, out returnedToken))
        {
            token = new IntPtr(returnedToken);
        }

    }

    [WebMethod]
    public DataSet GetDataSet(string id)
    {
        DataSet ds = null;

        Login();
        impersonationContext = WindowsIdentity.Impersonate(token);

        SqlConnection conn = null;
        SqlDataAdapter da = null;
        try
        {
            string sql = "Select * from Table";
            conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=True;" +
                    @"AttachDbFilename=|DataDirectory|\ORLDatabase.mdf;");
            conn.Open();
            da = new SqlDataAdapter(sql, conn);
            ds = new DataSet();
            da.Fill(ds, "Table");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);

        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }

        impersonationContext.Undo();
        return ds;
    }
}

Matthew Sposato · Accepted Answer · 2009-10-30 17:50:26Z

2

The windows account you created needs to be a login on the database engine as well. In SQL Server Management Studio: servername-->Security-->Login | Right Click --> New Login. I doubt file permissions are sufficient.

answered Oct 30, 2009 at 17:50

Matthew Sposato

1,6351 gold badge11 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slawomir Brys · Accepted Answer · 2014-01-25 00:35:47Z

1

I had issues with above solution, but with some changes it works.

Variable machine is not a machine name in API, but a domain. In my case I had to change it to domain to have successful call LogonUser WinAPI function.
Also in my case I had to change 3rd parameter of LogonUser function from 3 [LOGON32_LOGON_NETWORK] to 2 [LOGON32_LOGON_INTERACTIVE].

After that changes my local user on which my site is running successfully could connect to the database with my domain credentials.

answered Jan 25, 2014 at 0:35

Slawomir Brys

3613 silver badges5 bronze badges

Collectives™ on Stack Overflow

Windows impersonate [SQL, ASP.NET, C#]

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related