Connection Strings Frequent Password Changes Help

[Chuck]

Our corporate overlords require database password changes every 3 months.
With 60 plus websites hitting databases this is a pain.
Also we have separation of duties requirements:
. only the dba knows the password
. only the system admin can read/write to the web.config
. developers can't do squat

Currently we use webdeployment projects and swap out the connection strings
during build. We use SQL accounts for the db access. We use Forms
Authentication. The build also encrypts the connection strings using our own
RSA key. This won't work anymore, since the developers can't touch or know
the passwords.

Any suggestions on an efficient way to deploy/update while maintaining the
separation of duties?

Maybe have the IIS account run as a win account and give that permission to
the db using integrated? Won't need to update web.config but now you have a
domain account with many more permissions (not so good).

Maybe have external connection string file specified in the web.config.
Harder to update for 60 sites. Still need dba to encrypt and give file to
sysAdmin. Slow, site will be down for a while.

Other ideas?

 

[Allen Chen [MSFT]]

Hi,

Our corporate overlords require database password changes every 3 months.
With 60 plus websites hitting databases this is a pain.
Also we have separation of duties requirements:
. only the dba knows the password
. only the system admin can read/write to the web.config
. developers can't do squat

Currently we use webdeployment projects and swap out the connection strings
during build. We use SQL accounts for the db access. We use Forms
Authentication. The build also encrypts the connection strings using our own
RSA key. This won't work anymore, since the developers can't touch or know
the passwords.

How about using an HttpModule to change connectionstrings?

protected void Application_BeginRequest(object sender, EventArgs e)
{
// Hack way to update ConnectionString in memory. In real case
please loop through and update all
// ConnectionStrings to use new password
ConnectionStringsSection css =
(ConnectionStringsSection)WebConfigurationManager.GetWebApplicationSection("
connectionStrings");
var settings =
css.ConnectionStrings["NorthwindConnectionString"];
var field = typeof(ConfigurationElement).GetField("_bReadOnly",
BindingFlags.Instance | BindingFlags.NonPublic);
field.SetValue(settings,
false);
// You can get the new password from a local file or on another
machine that dba has control over.
// Or call a web service to get it for advanced usage and
flexibility.

css.ConnectionStrings["NorthwindConnectionString"].ConnectionString =
"newone";

}

For more details about HttpModule, please refer to:

http://msdn.microsoft.com/en-us/library/aa719858(VS.71).aspx

Please have a test and let me know if it works.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Joe Kaplan]

My preference is to use Windows auth where possible. You can still use
network service as IIS WP account. This account locally will appear to SQL
as the AD computer account for the machine in the domain, so you can ACL SQL
based on that.

An advantage of network service is that no one knows the password for the
computer account, so only services configured on the server as network
service (or system) can access the SQL db.

If you had a bunch of sites and felt it necessary to have separate accounts
gaining access to SQL, you can configure individual domain accounts as IIS
service accounts. Of course, if they make you change passwords on service
accounts, then you have a similar problem with changing passwords, but this
time in IIS (although managed service accounts in AD 2008 R2 can help with
this!). My preference would be to use role-based security in SQL for
authorization and just map the required windows principals to the required
roles.

The advantage with Windows auth is that the developers actually don't have
to have anything to do with it but admins don't have to mess with the
web.config either, making your build processes much more reasonable.

If you are squeamish about taking a dependency on Windows security for
authentication, then this is not a good match for you.

 

[Allen Chen [MSFT]]

Hi,

Our corporate overlords require database password changes every 3 months.
With 60 plus websites hitting databases this is a pain.
Also we have separation of duties requirements:
. only the dba knows the password
. only the system admin can read/write to the web.config
. developers can't do squat

Currently we use webdeployment projects and swap out the connection strings
during build. We use SQL accounts for the db access. We use Forms
Authentication. The build also encrypts the connection strings using our own
RSA key. This won't work anymore, since the developers can't touch or know
the passwords.

Can my suggestion help to solve this issue?

Regards,
Allen Chen
Microsoft Online Support

 

[Guest]

Our corporate overlords require database password changes every 3 months.
With 60 plus websites hitting databases this is a pain.
Also we have separation of duties requirements:
 .  only the dba knows the password
 .  only the system admin can read/write to the web.config
 .  developers can't do squat

Currently we use webdeployment projects and swap out the connection strings
during build. We use SQL accounts for the db access.  We use Forms
Authentication. The build also encrypts the connection strings using our own
RSA key.  This won't work anymore, since the developers can't touch or know
the passwords.

Any suggestions on an efficient way to deploy/update while maintaining the
separation of duties?

Maybe have the IIS account run as a win account and give that permission to
the db using integrated? Won't need to update web.config but now you havea
domain account with many more permissions (not so good).

Maybe have external connection string file specified in the web.config.  
Harder to update for 60 sites.  Still need dba to encrypt and give fileto
sysAdmin.  Slow, site will be down for a while.

Other ideas?

How about using registry?

Here's an example of the class to use registry
http://forums.asp.net/t/255840.aspx