StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWeb service connection to SQL Server with AD account
text
Body
copied!<p>I have a WCF web service that should always use a specific AD account, which has been granted access to the database, to execute SQL transactions. I read a couple of articles, but I'm obviously doing/understanding something wrong because I'm not getting it to work the way I want.</p> <p>I figured that that web service should impersonate the AD user, so I enabled impersonation in the web service web.config:</p> <pre><code><identity userName="dmn\wsusr" password="p@55w0rd" impersonate="true"/> </code></pre> <p>Then, since I'm technically using a Windows user to connect to SQL, I set the connection string as follows ("<code>Integrated security=true;</code>" for Windows authentication, right?):</p> <pre><code>Data Source=SQLSVR\INSTNC; Failover Partner=SQLSVR\INSTNC2; Initial Catalog=DB; Integrated Security=true; </code></pre> <p>For testing the connection I insert some values into a table. One of the columns of the table I'm inserting to has the following definition:</p> <pre><code>[LogUser] VARCHAR(75) NOT NULL DEFAULT USER </code></pre> <p>So, theoretically, the AD username of the user who opened the connection will automatically be inserted into the column. Unfortunately, however, the column contains my own AD username every time.</p> <p>I'm testing the web service via a web site that uses Windows authentication, so I'm assuming that this plays a role in the cause of the problem. But the website authentication should be disregarded since this will be an externally accessible web service and SQL transactions should never rely on authentication between the client and the web service.</p> <p>Thanks!</p> <p><strong>EDIT</strong></p> <p>I also tried:</p> <ul> <li>Adding <code>Trusted_connection</code> to the connection string, but it yielded the same result as above.</li> <li>Using <code>User ID</code> and <code>Password</code> in the connection string, but since the connection string only accepts SQL users, this resulted in a <code>Login failure</code> error</li> </ul> <p><strong>EDIT2</strong></p> <p>I suggested to my superiors that we should try the approach where you create a separate application pool for the service, set it up to run as the AD user, and allow the AD user to log on as a service (something I read somewhere) but they're <em>not</em> keen on that and reckon it should be a "last resort"</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload