9 Replies - 2853 Views - Last Post: 16 July 2012 - 07:36 PM Rate Topic: -----

#1 Goodfix86  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 01-July 09

Entity Framework and Sql Server

Posted 11 April 2012 - 10:55 AM

Please correct me if I'm wrong... and if I am please point me to a good resource.

Entity Framework Data Models can tell that password information is sensitive, but cannot do anything about it on their own. So if you are working in an organization where the server is not using integrated security and you HAVE TO pass the username/password of a sys admin then Entity Framework is not an option because it will show your sys admin username and password to every person who happens to stumble across the config file because it is in plain text.

I found out there is a way to encrypt some of the config file, but you have to sing "Baby got back" while balancing on the top of your head using one hand to rub your belly in circles and while using your off handed foot to manipulate the mouse.

For a feature that is as powerful as Entity Framework to be so clumsy with something this basic is like designing the world's fastest airplane and forgetting to add room for the fuel tank.

That is unless someone can point me to a resource for protecting the credentials of the connection string that allows EF to function normally that isn't 15 pages long.

Tech#1:"I've got a great idea. Let's store the username and password for the connection in a plain text file."
IT Manager:"Sounds great! Let's do it."
--- Something similar to this conversation must have taken place at Microsoft.

Is This A Good Question/Topic? 0
  • +

Replies To: Entity Framework and Sql Server

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,739
  • Joined: 12-June 08

Re: Entity Framework and Sql Server

Posted 11 April 2012 - 11:08 AM

Usually IIS blocks the web.config file from being accessed via a browser.. Is that what you are worried about?
Was This Post Helpful? 1
  • +
  • -

#3 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: Entity Framework and Sql Server

Posted 11 April 2012 - 11:16 AM

This may help you out. It's an article from Microsoft on the Security Considerations of Entity Framework.

http://msdn.microsof...y/cc716760.aspx
Was This Post Helpful? 1
  • +
  • -

#4 Goodfix86  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 01-July 09

Re: Entity Framework and Sql Server

Posted 12 April 2012 - 07:24 PM

Thank you. Both of your posts are helpful, however, I dropped EF in favor of using a datatable with a SQLdatareader and using a parametrized update query through SQLcommand.executenonquery. I might have spelled that wrong, but I don't have visual studio open right now. I used a foreach statement on the datatables rows and fed the id number into the where portion of the SQL statement on each round of the loop. It works. Can't be that bad if it works, right? Another good thing is that the username and password is buried inside the compiled code.

@Modi123_1: Thanks for the heads up on IIS. I'll have to look into that. I might launch my next project in a browser instead.

@Eclipsed4utoo: I have already spent some time in the pages you have linked, but it's still good advice. I think that what I might do when I have a little more downtime is create a project with nothing but the EF in it and overload the connection string inside the constructor. I have read that this can be done, but I was pressed for time. Consequently, being pressed for time and finding out that the technology that is supposed to be more productive was actually less productive for me turned into a big source of frustration for me. Still, thanks man!

For those of you who might be reading this after the fact, using an adapter or the SQLcommandbuilder didn't work out because the table was an inner join from two tables on the server. That wasn't too bad getting the data in, but it was not fun trying to get the update back to the server.
Was This Post Helpful? 0
  • +
  • -

#5 Goodfix86  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 01-July 09

Re: Entity Framework and Sql Server

Posted 04 June 2012 - 11:48 AM

I figured it out, but I forgot to return. Sorry everyone. This is what you have to do if you want to bury the password in the code. I am going to assume that you have already created an entity model with all the necessary associations and such.

Need these in your header.
using System.Configuration;
using System.Data.EntityClient;


Need this early on in your app to set the stage. This tosses the connection string through the connection stringbuilders. We start general, move more specific, set the password and then pop it back out the way we came.
ConnectionStringSettings myConfigString = 
   ConfigurationManager.ConnectionStrings["[i]Insert Connection Name Here[/i]"];
var efStringBuilder = new EntityConnectionStringBuilder(myConfigString.ConnectionString);
System.Data.SqlClient.SqlConnectionStringBuilder mySqlBuilder = 
   new System.Data.SqlClient.SqlConnectionStringBuilder(myEfStringBuilder.ProviderConnectionString);
mySqlBuilder.Password = "[i]Insert Password Here[/i]";
myEfStringBuilder.ProviderConnectionString = mySqlBuilder.ConnectionString;



Later when it comes time to query pass the connection string as a parameter to the datacontext.
var currentContext= new EntityModelName.DataContext(myEfStringBuilder.ConnectionString);

Was This Post Helpful? 1
  • +
  • -

#6 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1181
  • View blog
  • Posts: 1,676
  • Joined: 24-August 11

Re: Entity Framework and Sql Server

Posted 04 June 2012 - 12:05 PM

Thanks for coming back and sharing your solution.
Was This Post Helpful? 0
  • +
  • -

#7 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 3589
  • View blog
  • Posts: 11,157
  • Joined: 05-May 12

Re: Entity Framework and Sql Server

Posted 05 June 2012 - 12:16 PM

Thanks for coming back to post your solution, but aren't you just punting the issue? Now instead of the password being in plain text in the configuration file, now it is in plain text in your source code. Unless you have a very good obfuscator, somebody can still find your password in your IL. And if not the IL, if they have source code or source control access, the password is still out there.

Or is it a matter of just deterring the casual user/hacker? A determined hacker will invest the required number of hours/resources to find the password; a charming hacker will use guile and social engineering; and some (most?) organizations wouldn't think twice about rubber hose hacking.
Was This Post Helpful? 0
  • +
  • -

#8 Goodfix86  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 01-July 09

Re: Entity Framework and Sql Server

Posted 09 June 2012 - 08:44 PM

You are absolutely right. It is right there in the source code and the intermediate language. I think that this solution is viable for me due to circumstances surrounding the project I am working on, but could be pretty high risk for anyone publishing a program for consumers or something that has to connect from another network.

That would probably be good material for a thread on security. Maybe I'll start one and see what people have to offer.
Was This Post Helpful? 0
  • +
  • -

#9 Michael26  Icon User is online

  • DIC-head, major DIC-head
  • member icon

Reputation: 359
  • View blog
  • Posts: 1,527
  • Joined: 08-April 09

Re: Entity Framework and Sql Server

Posted 16 July 2012 - 12:27 PM

Quote

Please correct me if I'm wrong... and if I am please point me to a good resource.

Entity Framework Data Models can tell that password information is sensitive, but cannot do anything about it on their own


You could use Encrypting ConnectionStrings Programmatically in App.config
This link
Was This Post Helpful? 0
  • +
  • -

#10 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 444
  • View blog
  • Posts: 1,492
  • Joined: 28-April 09

Re: Entity Framework and Sql Server

Posted 16 July 2012 - 07:36 PM

Using Microsoft's Enterprise Library it's pretty simple to encrypt your connection strings.

Encrypting Configuration Data

Link to Enterprise Library:
Patterns & Practices: Enterprise Library


Attached Image

After installing Enterprise Library, it's a fairly simple 4 or 5 steps to encrypt the connection strings inside the web.config or in an app.config. No worries about someone viewing it in the source code or the web.config.

This post has been edited by Nakor: 16 July 2012 - 07:43 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1