Using a function instead of web.config to store connectionstring

[Jim Andersen]

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

[S. Justin Gengo [MCP]]

Jim,

It's a much longer way around but you may want to bind an object to the grid
view instead. There was an article on doing this in the January Visual
Studio Magazine and I gave it a try. It works wonderfully. I used the
ObjectDataSource for my grid. It required creating an object to bind to and
using Generics.List to create a list of said items. But it gave me the
ability to use all the non-programmatic grid controls (as long as my object
exposed the proper methods). Using this method you could connect to your
database any way you'd like.

If you don't get Visual Studio Magazine here are a couple of links to
different articles that show how to use the ObjectDataSource:

http://www.c-sharpcorner.com/Code/20...DataSource.asp

http://www.asp.net/QuickStart/aspnet...atasource.aspx

Neither of these articles use Generics like the Visual Studio Magazine
article did though. I think you can sign up to access it on their website,
www.visualstudiomagazine.com, they use a "Locator Code". The number for the
article is: VS0601JB_T

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche
"Jim Andersen" <no****@nospam.dk> wrote in message
news:Ow**************@TK2MSFTNGP12.phx.gbl...

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

[Ken Cox - Microsoft MVP]

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/downlo...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:\Prog ram 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_DataConnectionStr ing1
%>"
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_DataConnectionStr ing1.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="DataProtectionConfigurat ionProvider">
<EncryptedData>
<CipherData>
<CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAvr6cdqmKpka7y4ANmye/agQAAAACAAAAAAADZgAAqAAAABAAAACbkzyh+9L59AVsWp1bn8 2FAAAAAASAAACgAAAAEAAAABp/xn/8HNqFjsuaLbZh9mfIAwAAR2T/I3+F9GlSFg7Xobuy5PgowpxKUztdni9bmqi/JSgWtSxq4ziH+YQRo2FxYBhfdS54nGmd01O7gEE+B1SPYA/bRn7pd6O+ZndTJ38CzOFj9vW17HWlQO2QX13B7yiUVOiQYJJwP dpjjCNZNT5voItZEHrp5L9UWf+lI6Jpv/BTPDQBPH+OX9sq3mpDdkfrySC/Jdt6pqhKnlab6IywRtQYvR4YTtnO0yxSnh9PM9CUbCIKELWS9g u1mGAzQYVRm/RxRI4C1AXk8GoMw9kr1o385JP0e6VvqdlxdReGuWYfmBbAzxPK hPGp/YhQgvnuvz7g11QnMbq8YlYOjIOaXvNFYR9kZAVkbYzTy9p0b9L lPegc5PtEvlTzyUXTN3lub52UB1bz5E8PpPr+E4Tuu86N7c5dy nXpNGax+PsdzhZ/+/Dw93RLIVuPIod9VielYRt8IiDQqI54gmKq/ufxxri2vH0VnSMvj1eQHBtSyAM04WsodoZS6SARQWnN6HPPNGm imPpY+nrKuMEEd0g6fv2YM//aa57Y351NzUaduhvXJIgjiRDjDLa0IwU9wCF0NKBibPJQmJDj/kD0yY1ct8V3THqALV9ptZp0Zh7YosbtdN/xROca2H550cr2bpKl7X5+oVcvp7pXZk2tCm7V/rVIfUdb8YbDfWvNEO5RoWK7tJWiD8ZoGZ+5q4bQu8lMCuHPHMX hryyQ7kyhMrJWAjH3+WDulPaGRhS5v6A68lWeEol0x5KfwDZ/gHWsFd5hc08pfarNInWbmnwnx8nf9QVY8ub8xb8Ep8lQBxEUXE kmEPrSr7PrhKGuDTImuDvwAtvrxI04oZ1hvXL6I7FVAH0ZOgcL cnrbgflMmvJ8A1/3rllfNmE6nmoHyQi9ZPuGq5Ro1cy66GD53Tb++Q0IkErfJj6qt iHhiJrYswzT1FHq+sdyV1j1JKcbiK2Bi2PtlTaKo0ZMan3QqBh vSWnOyN7pguoKT62puRtvJgK5OVXZQ0mgi0U+i5Eqp8+MT9hwb 4Hp9QPSEVBnzlJStTOw8kTKXYtbA8OBAqMe3IG3Obshzs7YQCc WJbXkY5GK+BFDy2x80xbWSxmM7qcL6BgWKOm6+wd3OixeBLp16 xQ4HG+Sc1AhK+t5Zq5mp6mc508FeDpBA4HSoSqcBUPHF5PVStg QKEqMUX8Mz0g2BWyMYG15UbjvuBT7pmiBYXChm+c7rSb+FjW+r abpfuyNlnP0raENQ6tUsJZr6MGKKzqQdiWwCVT9McyU6YPBxNW oTwCKZc+ueBk6YTkUAAAAH0zOlWabm9II/PQgC5sPjR5Lcko=</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>
"Jim Andersen" <no****@nospam.dk> wrote in message
news:Ow**************@TK2MSFTNGP12.phx.gbl... 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

[Jim Andersen]

"Ken Cox - Microsoft MVP" <BA**********@hotmail.com> skrev i en meddelelse
news:ev**************@TK2MSFTNGP10.phx.gbl...

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/downlo...tips_final.wmv

Hi Ken,

Thanks for answering,

Yes I am aware of the built-in ProtectedConfigurationProviders.

From Overview of Protected Configuration:
http://msdn2.microsoft.com/en-us/lib...as(VS.80).aspx
"Both providers offer strong encryption of data"

Sounds good.

But I also read:
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication

http://msdn.microsoft.com/library/en...SecNetch12.asp

And read about the DPAPI, and got to the part:
"The machine store approach is easier to develop because it does not require
user profile management. However, unless an additional entropy parameter is
used, it is less secure"

And I seemed to run into the terms "improves security" and "adds extra
security" and "easily decodable" which I found pretty "fluffy". And I
couldn't find a description of just how strong the built-in encryption (from
your example) really is (or is not).

Most of the documentation focused on "how to" or "walkthrough"s, as the
first topic, instead of focusing on the techniques strengths and weaknesses.

I also was told by Microsoft that the Access database security was
"unbreakable" but now I can download a util that reveals all usernames and
passwords.

So I decided to roll my own encryption function. This way, I know what I am
getting. But I can see now, that I should be able to wrap it as a
ProtectedConfigurationProvider, and have asp.net use it, instead of the 2
built-in providers. Maybe I should look into that.

/jim