Re: Using a function instead of web.config to store connectionstring

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Jim,

Since you're using 2.0, you don't have to reinvent the wheel! The encryption
function is built in for you to store connection strings securely.

Here's a great tip from the Tips and Tricks in this video:

http://download.microsoft.com/download/8/3/6/836dd5f8-fa92-499f-8219-0d326f13bf18/hilo_tips_final.wmv

Add your connection string to your web.config as normal. For example, here's
mine:

<connectionStrings>
<add name="AdventureWorks_DataConnectionString1" connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=&quot;C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf&quot;;Integrated
Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

Create a page to do the encryption/decryption:

<%@ Page Language="VB" %>
<%@ import namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>

<script runat="server">

Protected Sub EncryptConfig(ByVal bEncrypt As Boolean)
Dim path = "~/"
' Use the WebConfigurationManager to open
' the local web.config file
Dim config As Configuration = _
WebConfigurationManager.OpenWebConfiguration(path)
' Get the connectionStrings section
' from the web.config file
Dim appSettings As ConfigurationSection = _
config.GetSection("connectionStrings")

If bEncrypt Then
' Encrypt the string using ProtectSection
appSettings.SectionInformation.ProtectSection _
("DataProtectionConfigurationProvider")
Else
'Decrypt the string using UnprotectSection
appSettings.SectionInformation.UnprotectSection()
End If
'Save the changes
config.Save()
End Sub
Protected Sub Button1_Click _
(ByVal sender As Object, ByVal e As System.EventArgs)
EncryptConfig(True)
End Sub

Protected Sub Button2_Click _
(ByVal sender As Object, ByVal e As System.EventArgs)
EncryptConfig(False)
End Sub


</script>

<html xmlns="http://www.w3.org/1999/xhtml"; >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:sqldatasource id="SqlDataSource1" runat="server"
connectionstring="<%$ ConnectionStrings:AdventureWorks_DataConnectionString1
%>"
deletecommand="DELETE FROM [Employee] WHERE [EmployeeID] =
@EmployeeID" insertcommand="INSERT INTO [Employee] ([NationalIDNumber],
[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus],
[Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours],
[CurrentFlag], [rowguid], [ModifiedDate]) VALUES (@NationalIDNumber,
@ContactID, @LoginID, @ManagerID, @Title, @BirthDate, @MaritalStatus,
@Gender, @HireDate, @SalariedFlag, @VacationHours, @SickLeaveHours,
@CurrentFlag, @rowguid, @ModifiedDate)"
providername="<%$
ConnectionStrings:AdventureWorks_DataConnectionString1.ProviderName %>"
selectcommand="SELECT [EmployeeID], [NationalIDNumber],
[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus],
[Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours],
[CurrentFlag], [rowguid], [ModifiedDate] FROM [Employee]"
updatecommand="UPDATE [Employee] SET [NationalIDNumber] =
@NationalIDNumber, [ContactID] = @ContactID, [LoginID] = @LoginID,
[ManagerID] = @ManagerID, [Title] = @Title, [BirthDate] = @BirthDate,
[MaritalStatus] = @MaritalStatus, [Gender] = @Gender, [HireDate] =
@HireDate, [SalariedFlag] = @SalariedFlag, [VacationHours] = @VacationHours,
[SickLeaveHours] = @SickLeaveHours, [CurrentFlag] = @CurrentFlag, [rowguid]
= @rowguid, [ModifiedDate] = @ModifiedDate WHERE [EmployeeID] = @EmployeeID"

<insertparameters>
</asp:sqldatasource>
<br />
&nbsp;<asp:button id="Button1" runat="server"
onclick="Button1_Click" text="Encrypt" />
<asp:button id="Button2" runat="server" text="Decrypt"
onclick="Button2_Click" /></div>
</form>
</body>
</html>

When you click the button, it'll rewrite your web.confg so it comes out like
this:

<connectionStrings
configProtectionProvider="DataProtectionConfigurationProvider">
<EncryptedData>
<CipherData>
<CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAvr6cdqmKpka7y4ANmye/agQAAAACAAAAAAADZgAAqAAAABAAAACbkzyh+9L59AVsWp1bn82FAAAAAASAAACgAAAAEAAAABp/xn/8HNqFjsuaLbZh9mfIAwAAR2T/I3+F9GlSFg7Xobuy5PgowpxKUztdni9bmqi/JSgWtSxq4ziH+YQRo2FxYBhfdS54nGmd01O7gEE+B1SPYA/bRn7pd6O+ZndTJ38CzOFj9vW17HWlQO2QX13B7yiUVOiQYJJwPdpjjCNZNT5voItZEHrp5L9UWf+lI6Jpv/BTPDQBPH+OX9sq3mpDdkfrySC/Jdt6pqhKnlab6IywRtQYvR4YTtnO0yxSnh9PM9CUbCIKELWS9gu1mGAzQYVRm/RxRI4C1AXk8GoMw9kr1o385JP0e6VvqdlxdReGuWYfmBbAzxPKhPGp/YhQgvnuvz7g11QnMbq8YlYOjIOaXvNFYR9kZAVkbYzTy9p0b9LlPegc5PtEvlTzyUXTN3lub52UB1bz5E8PpPr+E4Tuu86N7c5dynXpNGax+PsdzhZ/+/Dw93RLIVuPIod9VielYRt8IiDQqI54gmKq/ufxxri2vH0VnSMvj1eQHBtSyAM04WsodoZS6SARQWnN6HPPNGmimPpY+nrKuMEEd0g6fv2YM//aa57Y351NzUaduhvXJIgjiRDjDLa0IwU9wCF0NKBibPJQmJDj/kD0yY1ct8V3THqALV9ptZp0Zh7YosbtdN/xROca2H550cr2bpKl7X5+oVcvp7pXZk2tCm7V/rVIfUdb8YbDfWvNEO5RoWK7tJWiD8ZoGZ+5q4bQu8lMCuHPHMXhryyQ7kyhMrJWAjH3+WDulPaGRhS5v6A68lWeEol0x5KfwDZ/gHWsFd5hc08pfarNInWbmnwnx8nf9QVY8ub8xb8Ep8lQBxEUXEkmEPrSr7PrhKGuDTImuDvwAtvrxI04oZ1hvXL6I7FVAH0ZOgcLcnrbgflMmvJ8A1/3rllfNmE6nmoHyQi9ZPuGq5Ro1cy66GD53Tb++Q0IkErfJj6qtiHhiJrYswzT1FHq+sdyV1j1JKcbiK2Bi2PtlTaKo0ZMan3QqBhvSWnOyN7pguoKT62puRtvJgK5OVXZQ0mgi0U+i5Eqp8+MT9hwb4Hp9QPSEVBnzlJStTOw8kTKXYtbA8OBAqMe3IG3Obshzs7YQCcWJbXkY5GK+BFDy2x80xbWSxmM7qcL6BgWKOm6+wd3OixeBLp16xQ4HG+Sc1AhK+t5Zq5mp6mc508FeDpBA4HSoSqcBUPHF5PVStgQKEqMUX8Mz0g2BWyMYG15UbjvuBT7pmiBYXChm+c7rSb+FjW+rabpfuyNlnP0raENQ6tUsJZr6MGKKzqQdiWwCVT9McyU6YPBxNWoTwCKZc+ueBk6YTkUAAAAH0zOlWabm9II/PQgC5sPjR5Lcko=</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>


"Jim Andersen" <nospam@xxxxxxxxx> wrote in message
news:OwlqgbTPGHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

I would appreciate if someone could explain this behaviour, and maybe
offer a better solution.

I have been working with the GridView control. And SqlDataSource. It works
great if I do:
<asp:SqlDataSource ConnectionString="yada yada yada" etc etc />.

I can hook up a GridView to the sqldatasource and view/edit/add records.

But this isn't very secure.
I saw that I could do:
ConnectionString="<$ point_to_web.config key >"
It also works. And I can do some weak encryption of the web.config.

But I have a function that decrypts a strongly encrypted ConnectionString,
so I wanna use that.

I found out that I could do:

ConnectionString="<%# GetConnStr() %>"

However, that only works if I in the page_load do:
Databind()

But now I can't do any edit's in the GridView. And if I remove the
Databind(), (or even wrap it in a "if not ispostback") I get a
"connectionstring not initialized" error message.

So in my page_load I now do
if me.gridview1.sqldatasource <> GetConnStr() then
me.gridview1.sqldatasource <> GetConnStr()
endif

but I don't think it's "clean" and it might get me into trouble later ?
Like the DataBind() that screwed up my editing capabilities.

/jim



.

Quantcast