1

In my testing environment, I have a local sql express (2008R) server instance. I've created a domain group and given it a login in the database.

But now, how do I consctruct the connection string for my web-application? Do I give it the username and password of one of the members of the group?

And, what do I put for a "user" in the Database security section? The group itself doesn't have a password.

Do I have to add the service account from the application pool to the AD group?

Here is what I have for a connection string; but this allows all connections, it seems:

<add name="development" connectionString="server=myserver\sqlexpress;
    database=mydatabase;Persist Security Info=True;Integrated Security=SSPI;
    Trusted_Connection=True; Pooling=True" providerName="System.Data.SqlClient"
/>
Community
  • 1
bgmCoder
  • 119
  • 7

1 Answers1

3

You need to configure the IIS Application Pool to use the identity (domain user) you created.

My example below shows the default, out-of-the-box configuration. You'd need to choose 'Configurable' and specify the username and password there.

Then you need to configure the site to use the AppPool.

enter image description here

The connection string would then be:

server=myserver\sqlexpress;database=mydatabase;Persist Security Info=True;
Integrated Security=SSPI;Trusted_Connection=True; Pooling=True

Just like you have it.

Hannah Vernon
  • 66,859
  • 22
  • 158
  • 297
  • Hi, Mr. Vernon; what if, on my dev machine, the service is the VS virtual server - it's not in iis? – bgmCoder Apr 19 '13 at 21:34
  • Good question. There must a similar way to do this. Perhaps you need to start Visual Studio as the user that has access to the database. I'm not certain. – Hannah Vernon Apr 19 '13 at 21:35
  • I just fired up VS... will see if there is a way to do. – Hannah Vernon Apr 19 '13 at 21:37
  • I understand what you mean, though for the application pool - that will probably work on the server. I'll try and see if I can get VS to run in IIS on my dev machine instead of using the virtual server - and see what happens. – bgmCoder Apr 19 '13 at 21:37
  • Problem is that I can't add a local service account (VS) to a domain group... – bgmCoder Apr 19 '13 at 21:38
  • Yeah, it appears you'd want to install the full IIS product on your workstation, and configure VS to use it. That way you are closely mimicking the production environment. – Hannah Vernon Apr 19 '13 at 21:39
  • Unless I could change the account used by the VS virtual server, no? – bgmCoder Apr 19 '13 at 21:41
  • I would try running Visual Studio as the domain user account you specified in SQL Server. Start Visual Studio from a command prompt using `RUNAS ...` – Hannah Vernon Apr 19 '13 at 21:41
  • Okay, I hooked the site up to my IIS and changed the application pool to run on that account. But with that connection string, it seems to connect every time. I should be able to control it with the group membership. Now, how do I get to NOT connect so I can prove that the group membership makes a difference in the sql connection? – bgmCoder Apr 19 '13 at 22:09
  • if you want the group to control access to Sql server, you need to remove the login from sql server and add the group itself to sql server instead. – Hannah Vernon Apr 19 '13 at 22:42
  • That's what I have done, actually. I've added the group to the logins, and mapped a user to the *group* in order to give it access to the database. But with the connection string I have in my question, I can *always* connect, even if I remove the application pool account from the AD group. – bgmCoder Apr 19 '13 at 22:46
  • So I set `Persist Security Info=false` and restarted the app pool, but it still connects. – bgmCoder Apr 19 '13 at 22:50
  • you may need to run iisreset to recycle iis – Hannah Vernon Apr 19 '13 at 23:31
  • That doesn't do it either. How is it getting access? This is hard! – bgmCoder Apr 20 '13 at 00:23
  • I posted a link in Stack Exchange: http://stackoverflow.com/questions/16115382/using-sspi-how-to-make-my-webapp-not-connect-to-local-sql-express – bgmCoder Apr 20 '13 at 00:48