.Net, IIS, Programming, Web, XML

Using Encryption and Decryption on a .Net Web.Config ConnectionString

If you have .Net web applications that are connecting to data sources that you do not want other users to know about then chances are it is about time to start encrypting the connection strings. Why is this important? If you have an external website that fails (and you do not have any custom error pages) then you may expose connection information in the stack trace or error messages. Also, you do not want to allow other developers that stumble upon your connection string to see your connection information in clear text. Over time and many applications later I went from trying to remember paths and commands and have created batch files to perform this on a minimal web.config.

I am going to walk through how to create some encrypt/decrypt batch files and how these are used in conjunction with your web.config.

  • Ensure you have a .Net Framework installed to use the aspnet_regiis.exe program. For this example we are using .Net Framework 4.0.
  • Create a folder for all of your files to sit inside of. For this demo let’s call the folder “decrypter”. Make note of where you are storing this folder to use in your batch files in the next steps. For this example let’s assume “C:\decrypter”
  • Open notepad and create a file named Decrypt.bat and add the following information (take note of the path you’ll need to update based on where you are going to store your folder):
echo Decrypting connection strings
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" "C:\decrypter"
Pause
  • Open notepad again and create a file named Encrypt.bat and add the following information (take note of the path you’ll need to update based on where you are going to store your folder):
echo Encrypting connection strings
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" "C:\decrypter"
Pause
  • Open notepad again and create a web.config file and insert your connectionstrings section inside (make sure to place this file in the same location as the batch files, you’ll use this over and over):
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
 <add name="Demo" connectionString="Data Source=123.45.67.89;Initial Catalog=DemoCatalog;Persist Security Info=True;User ID=specialuser;Password=specialpassword" providerName="System.Data.SqlClient" />
 </connectionStrings>
</configuration>
  • Once you have that saved, run your Encrypt.bat as administrator. You should see the following:

en-example

  • Your connection strings are now encrypted. Review your web.config and see:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
 <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
 xmlns="http://www.w3.org/2001/04/xmlenc#">
 <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
 <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
 <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
 <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
 <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
 <KeyName>Rsa Key</KeyName>
 </KeyInfo>
 <CipherData>
 <CipherValue>You'll Have A Cipher Here.</CipherValue>
 </CipherData>
 </EncryptedKey>
 </KeyInfo>
 <CipherData>
 <CipherValue>You'll Have A Cipher Here.</CipherValue>
 </CipherData>
 </EncryptedData>
</connectionStrings>
</configuration>
  • You can now take the connectionStrings section and replace your cleartext version for your application.
  • Should you ever need to update your connectionStrings simply place the encrypted version back on your minimal web.config and run the Decrypt.bat (as administrator). This is what you should see:

de-example

  • You should then see your original clear text connection string. Hope this helps.
  • Things of note:
    • You can also do this with other sections of the web.config (just rename connectionStrings to whatever section you need to encrypt)
    • If you have comments inside of your connectionString the encryption and decryption will remove them.
    • Another reference: https://msdn.microsoft.com/en-us/library/zhhddkxy.aspx
BCS, SharePoint, SQL, XML

How To Make A SharePoint List System.String Column (Built From An BCS External Content Type List) Into A Clickable Hyperlink

So for this example I have a SQL database that I am pulling into an external content type called OfficeLocations. From this external content type I have created a SharePoint list that is referencing it. The issue I ran into is that the GoogleMapLink text coming from SQL was in a NVARCHAR format (which was System.String on the external content type and Single line of text for the column) and you cannot modify those columns.

officelocationslistsetting

This left me with a list that had a link but it was not clickable:

officelocationslistsetting1

Annoying right? So what do we do? Let’s mess with the XSL template of the item and see what happens…so I popped into SharePoint designer and created a view off of the SharePoint list and modified the XSL template of the GoogleMapLink column to use the string field as the href of the <a> tag and supplied my own text to give the link a more user friendly readable URL.

To the code (the bolded elements are the only additions I made):

<xsl:template name="FieldRef_Text_body.GoogleMapLink" ddwrt:dvt_mode="body" match ="FieldRef[@Name='GoogleMapLink']" mode="Text_body" ddwrt:ghost="hide">
 <xsl:param name="thisNode" select="."/>
 <xsl:variable name="currentValue" select="$thisNode/@*[name()=current()/@Name]" />
 <xsl:choose>
 <xsl:when test="@AutoHyperLink='TRUE'">
 <xsl:value-of select="$thisNode/@*[name()=current()/@Name]" disable-output-escaping ="yes"/>
 </xsl:when>
 <xsl:otherwise>
<strong><xsl:element name="a"></strong>
<strong> <xsl:attribute name="href"></strong>
 <xsl:value-of select="$thisNode/@*[name()=current()/@Name]"/>
<strong> </xsl:attribute></strong>
<strong> <xsl:text>Link</xsl:text></strong>
<strong> </xsl:element></strong>
 </xsl:otherwise>
 </xsl:choose>
 </xsl:template>

This created clickable links titled “Link” on the list itself. You can see the results below:

officelocationslistsetting2

Much cleaner, right? If you wanted the full link you could do that too. Hope that helps.