DB Connection String

[scancelli]

Hi, I'm a new able commerce user and I really like what I see so far. There's a lot to discover and learn and I guess full documentation is still not availalbe.

I'm trying to get a handle on the architechture. It looks like the connection string info is encrypted in the file ..\App_Data\database.config.

I can't find an obvious way of modifying this setting - and I need to be able to do it when moving db's to different servers. How is this handled and is it also possible to disable encryption for config files.

Thanks,
Stef Cancelli

[AbleMods]

Are you running a demo site?

My retail copy of AC7 RC1 does not ecrypt the database.config file. I easily modified it to include additional DB strings and modify the existing one when migrating from test to production.

[Shopping Cart Admin]

Hello Joe,

Please post the connection string example..without your personal connection information in it. All of the ones I can find are encrypted.

Depending on the permissions of the target installation, if the encryption fails we fall back to writing an un-encrypted version of the string.

[AbleMods]

<connectionStrings>
<add name="AbleCommerce" connectionString="Server=yourserver;
Database=yourDB;Uid=youruser;Pwd=yourPW;" ProviderName="System.Data.SqlClient" />
</connectionStrings>

There you go

[Shopping Cart Admin]

Thanks for posting!

[scancelli]

Thanks Joe for posting your connections string.

Mike, is there some external tool to set the connection string info? I need to move the db and change the uid and pwd.

Thanks,
Stef.

[Logan Rhodehamel]

1) Make a backup copy of database.config
2) Take a copy of the code that Joe posted.
3) Paste it into your database.config file.
4) Modify the connection string to have the correct new server, database name, username, and password information.
5) Save the updated database.config file.

Assuming your data has already been ported to the new database location, your installation will now be moved over.

[AbleMods]

So is my understanding correct that the system will automatically read both the unencrypted and encrypted versions?

Wouldn't it just be easier for everyone to make the encryption an option in the website screen? That way you could unencrypt it, move your site and re-enable encryption.

[Logan Rhodehamel]

So is my understanding correct that the system will automatically read both the unencrypted and encrypted versions?

Wouldn't it just be easier for everyone to make the encryption an option in the website screen? That way you could unencrypt it, move your site and re-enable encryption.

Yes, the config encryption is a .NET feature. It can read the file either way.

This area could definitely be handled better. I have logged a bug report to have it investigated.

[Logan Rhodehamel]

If you can connect to the web server where AbleCommerce is installed, you can handle encryption and decryption from the command line. You have to know the number of the website. "1" is the Default Web Site that exists when you install IIS. If you have created separate websites you'll need to provide the correct value for site.

Open Command Line Window
Change to .NET directory:

Code: Select all

cd \WINDOWS\Microsoft.NET\Framework\v2.0.50727

To Decrypt:

aspnet_regiis -pd "connectionStrings" -app "/YourAppFolder" -site "1"

To Encrypt:

aspnet_regiis -pe "connectionStrings" -app "/YourAppFolder" -site "1" -prov "DataProtectionConfigurationProvider"

More information about this procedure can be found here:

http://msdn2.microsoft.com/en-us/library/zhhddkxy.aspx

[scancelli]

Thanks Logan,

I appreciate this since I'd prefer to keep the config files encrypted on a production server.

Stef Cancelli.

[Logan Rhodehamel]

Also, I have produced a script that allows the connection string to be managed / updated from the merchant admin. I will try to make this available soon, so the command line instructions above are an interim solution.

[chazen]

I have a database.config file for AC7 with the same format posted above. I only have ftp access to my web server so I brought back the database.config file to my PC copied it to a Windows 2000 Server. I ran this command:

aspnet_regiis -pe "connectionStrings" -app "/store" -site "1"

I got a message saying that the encryption was successful.
The /store is the subdirectory on my shared hosting web stie and I also created a virtual path to /store on the Windows 2000 server that I ran the above command on. I noticed that it did not modify the database.config file but a new file named web.config got created. I ftp'ed this file back to my web server (hosted at godaddy.com). I renamed my original database.config file on the server to database.config.bak and I renamed the web.config on the server to database.config. But when I bring up my web site http://www.mysite.com/store I get an error message as follows:

Server Error in '/store' Application.
--------------------------------------------------------------------------------

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: The format of a configSource file must be an element containing the name of the section.

Source Error:


Line 1: <xml>
Line 2: <configuration>
Line 3: <connectionStrings>
Line 4: <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"


Source File: d:\hosting\schazen\store\App_Data\database.config Line: 2


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

When I look at the web.config file that got created that I renamed to database.config I do see an encryption string in it. The file looks as follows:

<xml>
<configuration>
<connectionStrings>
<EncryptedData>
<EncryptionMethod>
<KeyInfo>
<EncryptedKey>
<EncryptionMethod>
<KeyInfo>
<KeyName>Rsa Key</KeyName>
</KeyInfo>
<CipherData>
<CipherValue>P3oMjHzFTVtNO3vz5YYgAdI62aEkYBAdYDzHCB53yLTukaK+qBXkWLExugf0GNgNLUoMRjMbby4glFyC3vQsnBYoa/UClR9h168Wqz/uD5vc4LIUGmFZLGnV3NN7qb3Ek6bO0pCDOS1NnETn5p5EAVLMlpXRW5lTjzpLj5J9QV8=</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>JGRmK2rpjoB5d+PA9lyf+JEGgne6CTevH2wGAU4WAdE=</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
</configuration>

Once I get this working correctly I will chang my password and re-encrypt again.

[Logan Rhodehamel]

It will not be possible for you to encrypt the file on another machine and then upload. The encryption key used by .NET is machine specific. Your key is different from godaddy.

You will have to restore the original (unencrypted) config files to make the store work again. To encrypt you will have to use the script I mentioned above. I will check on this today.

[jmestep]

I'm not sure if this applies here, but maybe it will help. I've been going thru a C# book and it said on web farms you need to make the encryption key in the machine.config the same on all the servers. Maybe that will work when moving the site also?

[efficiondave]

Is there any way to programmatically access the connectionstring?

[Logan Rhodehamel]

Probably something like ConfigurationManager.ConnectionStrings["AbleCommerce"].ConnectionString if you've not changed the name of the connection string in the web.config file.

[efficiondave]

Perfect! That's what I needed.

I was skeptical that would work since the connection string isn't actually in the web.config but the web.config does point ConfigurationManager to the database.config file and that's what it needed.

Thanks!

[Logan Rhodehamel]

It works because we use an external mapping native to ASP.NET - so it's not anything proprietary. We put the connection string external to web.config because it moves us closer to the goal of only needing writable access to App_Data folder.

[efficiondave]

off topic, but if that's the goal, you need to move the data feeds out of App_Data so they can be accessed by feed crawlers like Google's.