Using a function instead of web.config to store connectionstrin g

[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:SqlDataSou rce ConnectionStrin g="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:
ConnectionStrin g="<$ point_to_web.co nfig 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 ConnectionStrin g,
so I wanna use that.

I found out that I could do:

ConnectionStrin g="<%# 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
"connectionstri ng not initialized" error message.

So in my page_load I now do
if me.gridview1.sq ldatasource <> GetConnStr() then
me.gridview1.sq ldatasource <> 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
ObjectDataSourc e 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 ObjectDataSourc e:

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******** ******@TK2MSFTN GP12.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:SqlDataSou rce ConnectionStrin g="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:
ConnectionStrin g="<$ point_to_web.co nfig 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 ConnectionStrin g,
so I wanna use that.

I found out that I could do:

ConnectionStrin g="<%# 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
"connectionstri ng not initialized" error message.

So in my page_load I now do
if me.gridview1.sq ldatasource <> GetConnStr() then
me.gridview1.sq ldatasource <> 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:

<connectionStri ngs>
<add name="Adventure Works_DataConne ctionString1" connectionStrin g="Data
Source=.\SQLEXP RESS;AttachDbFi lename=&quot;C: \Program Files\Microsoft SQL
Server\MSSQL.1\ MSSQL\Data\Adve ntureWorks_Data .mdf&quot;;Inte grated
Security=True;C onnect Timeout=30;User Instance=True"
providerName="S ystem.Data.SqlC lient" />
</connectionStrin gs>

Create a page to do the encryption/decryption:

<%@ Page Language="VB" %>
<%@ import namespace="Syst em.Web.Configur ation" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">

<script runat="server">

Protected Sub EncryptConfig(B yVal bEncrypt As Boolean)
Dim path = "~/"
' Use the WebConfiguratio nManager to open
' the local web.config file
Dim config As Configuration = _
WebConfiguratio nManager.OpenWe bConfiguration( path)
' Get the connectionStrin gs section
' from the web.config file
Dim appSettings As ConfigurationSe ction = _
config.GetSecti on("connectionS trings")

If bEncrypt Then
' Encrypt the string using ProtectSection
appSettings.Sec tionInformation .ProtectSection _
("DataProtectio nConfigurationP rovider")
Else
'Decrypt the string using UnprotectSectio n
appSettings.Sec tionInformation .UnprotectSecti on()
End If
'Save the changes
config.Save()
End Sub
Protected Sub Button1_Click _
(ByVal sender As Object, ByVal e As System.EventArg s)
EncryptConfig(T rue)
End Sub

Protected Sub Button2_Click _
(ByVal sender As Object, ByVal e As System.EventArg s)
EncryptConfig(F alse)
End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:sqldatasou rce id="SqlDataSour ce1" runat="server"
connectionstrin g="<%$ ConnectionStrin gs:AdventureWor ks_DataConnecti onString1
%>"
deletecommand=" DELETE FROM [Employee] WHERE [EmployeeID] =
@EmployeeID" insertcommand=" INSERT INTO [Employee] ([NationalIDNumbe r],
[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus],
[Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours],
[CurrentFlag], [rowguid], [ModifiedDate]) VALUES (@NationalIDNum ber,
@ContactID, @LoginID, @ManagerID, @Title, @BirthDate, @MaritalStatus,
@Gender, @HireDate, @SalariedFlag, @VacationHours, @SickLeaveHours ,
@CurrentFlag, @rowguid, @ModifiedDate)"
providername="< %$
ConnectionStrin gs:AdventureWor ks_DataConnecti onString1.Provi derName %>"
selectcommand=" SELECT [EmployeeID], [NationalIDNumbe r],
[ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus],
[Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours],
[CurrentFlag], [rowguid], [ModifiedDate] FROM [Employee]"
updatecommand=" UPDATE [Employee] SET [NationalIDNumbe r] =
@NationalIDNumb er, [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"

<insertparamete rs>
</asp:sqldatasour ce>
<br />
&nbsp;<asp:butt on id="Button1" runat="server"
onclick="Button 1_Click" text="Encrypt" />
<asp:button id="Button2" runat="server" text="Decrypt"
onclick="Button 2_Click" /></div>
</form>
</body>
</html>

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

<connectionStri ngs
configProtectio nProvider="Data ProtectionConfi gurationProvide r">
<EncryptedDat a>
<CipherData>
<CipherValue>AQ AAANCMnd8BFdERj HoAwE/Cl+sBAAAAvr6cdq mKpka7y4ANmye/agQAAAACAAAAAAA DZgAAqAAAABAAAA Cbkzyh+9L59AVsW p1bn82FAAAAAASA AACgAAAAEAAAABp/xn/8HNqFjsuaLbZh9m fIAwAAR2T/I3+F9GlSFg7Xobu y5PgowpxKUztdni 9bmqi/JSgWtSxq4ziH+YQ Ro2FxYBhfdS54nG md01O7gEE+B1SPY A/bRn7pd6O+ZndTJ3 8CzOFj9vW17HWlQ O2QX13B7yiUVOiQ YJJwPdpjjCNZNT5 voItZEHrp5L9UWf +lI6Jpv/BTPDQBPH+OX9sq3 mpDdkfrySC/Jdt6pqhKnlab6Iy wRtQYvR4YTtnO0y xSnh9PM9CUbCIKE LWS9gu1mGAzQYVR m/RxRI4C1AXk8GoMw 9kr1o385JP0e6Vv qdlxdReGuWYfmBb AzxPKhPGp/YhQgvnuvz7g11Qn Mbq8YlYOjIOaXvN FYR9kZAVkbYzTy9 p0b9LlPegc5PtEv lTzyUXTN3lub52U B1bz5E8PpPr+E4T uu86N7c5dynXpNG ax+PsdzhZ/+/Dw93RLIVuPIod9V ielYRt8IiDQqI54 gmKq/ufxxri2vH0VnSMv j1eQHBtSyAM04Ws odoZS6SARQWnN6H PPNGmimPpY+nrKu MEEd0g6fv2YM//aa57Y351NzUaduh vXJIgjiRDjDLa0I wU9wCF0NKBibPJQ mJDj/kD0yY1ct8V3THqA LV9ptZp0Zh7Yosb tdN/xROca2H550cr2bp Kl7X5+oVcvp7pXZ k2tCm7V/rVIfUdb8YbDfWvN EO5RoWK7tJWiD8Z oGZ+5q4bQu8lMCu HPHMXhryyQ7kyhM rJWAjH3+WDulPaG RhS5v6A68lWeEol 0x5KfwDZ/gHWsFd5hc08pfar NInWbmnwnx8nf9Q VY8ub8xb8Ep8lQB xEUXEkmEPrSr7Pr hKGuDTImuDvwAtv rxI04oZ1hvXL6I7 FVAH0ZOgcLcnrbg flMmvJ8A1/3rllfNmE6nmoHyQ i9ZPuGq5Ro1cy66 GD53Tb++Q0IkErf Jj6qtiHhiJrYswz T1FHq+sdyV1j1JK cbiK2Bi2PtlTaKo 0ZMan3QqBhvSWnO yN7pguoKT62puRt vJgK5OVXZQ0mgi0 U+i5Eqp8+MT9hwb 4Hp9QPSEVBnzlJS tTOw8kTKXYtbA8O BAqMe3IG3Obshzs 7YQCcWJbXkY5GK+ BFDy2x80xbWSxmM 7qcL6BgWKOm6+wd 3OixeBLp16xQ4HG +Sc1AhK+t5Zq5mp 6mc508FeDpBA4HS oSqcBUPHF5PVStg QKEqMUX8Mz0g2BW yMYG15UbjvuBT7p miBYXChm+c7rSb+ FjW+rabpfuyNlnP 0raENQ6tUsJZr6M GKKzqQdiWwCVT9M cyU6YPBxNWoTwCK Zc+ueBk6YTkUAAA AH0zOlWabm9II/PQgC5sPjR5Lcko= </CipherValue>
</CipherData>
</EncryptedData>
</connectionStrin gs>
"Jim Andersen" <no****@nospam. dk> wrote in message
news:Ow******** ******@TK2MSFTN GP12.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:SqlDataSou rce ConnectionStrin g="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:
ConnectionStrin g="<$ point_to_web.co nfig 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 ConnectionStrin g,
so I wanna use that.

I found out that I could do:

ConnectionStrin g="<%# 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
"connectionstri ng not initialized" error message.

So in my page_load I now do
if me.gridview1.sq ldatasource <> GetConnStr() then
me.gridview1.sq ldatasource <> 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**********@h otmail.com> skrev i en meddelelse
news:ev******** ******@TK2MSFTN GP10.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 ProtectedConfig urationProvider s.

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 "walkthroug h"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
"unbreakabl e" 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
ProtectedConfig urationProvider , and have asp.net use it, instead of the 2
built-in providers. Maybe I should look into that.

/jim