locked
EF Code First and SQL Connection Options RRS feed

  • Question

  • Hello everyone,

    I`m learning EF and I`m searching for examples for applications that Uses CODE FIRST Model.

    Most of the samples that I seen so far use Integrated Security for the connection string to the SQL DB.

    I was searching for examples that use an Interface that gives the user the option to connect to a given DB in SQL, BUT the user is allowed to choose between Integrated Security or SQL Login.

    The second part of this process is the strategy to where and how to encrypt these SQL configurations (if the user is using a SQL Login to store user and PW) so when the Application starts again it can read from that file and connect automatically to that DB without exposing the user + pw credentials in clear text in that file.

    What is the best way to do this minimizing the security concerns by storing sensitive information in a file in the local server.

    Thank you.

    • Moved by Kristin Xie Friday, April 1, 2016 8:01 AM Ado EF related
    Thursday, March 31, 2016 7:42 PM

Answers

  • Hello,

     Have you read this thread;

     https://social.msdn.microsoft.com/Forums/vstudio/en-US/440bdb23-c5ee-45df-b873-0b4f8197f9a3/ef-code-first-connection-string?forum=adodotnetentityframework

     There is a link to a walk-thru for EF Code model. Take a look at that also.

    https://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/ms191192.aspx

    https://support.microsoft.com/en-us/kb/316898

    https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/

     Thanks :)

    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Thursday, March 31, 2016 8:18 PM
  • Hi JPKI,

    I know there's a lot of tutorials out there through out the internet, a good start would be http://www.entityframeworktutorial.net/code-first/what-is-code-first.aspx

    I just want to add some points when choosing code first development.

    1. Code first development doesn’t rely on the entity framework to create EDMX and its mapping. So it might take you a long time to create them especially when you already set-up the database.
    2. It has a complete separation to the database you are working on. In the end, database is just a storage with no logic at all (which is good).
    3. Easy track for version control.
    4. Choosing code first development allows you to have a full control over the code.
    5. Advantageous in database migrations.
    6. You also have to take note that any manual changes to the database will actually screw your code because it defines the database.

    With regards to the authentication, you can use Membership provider. They already handle the roles and permissions + password hashing.

    These stuffs are also good in Web API development.
    You can also look at the following links:

    http://helios.ca/2009/04/22/aspnet-mvc-sqlmembershipprovider/

    https://msdn.microsoft.com/en-us/library/f1kyba5e.aspx

    Best Regards,

    Eric John Adamos


    Failure is not the worst thing in the world.

    The very worst is not to try.

    Email Address : ericjohnadamos@gmail.com

    http://ericjohnadamos.blogspot.com/



    • Edited by Eric John Adamos Friday, April 1, 2016 4:51 AM
    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Friday, April 1, 2016 4:49 AM
  • Ok, I found some good pointers here>

    Connection Strings and Configuration Files

    https://msdn.microsoft.com/en-us/library/ms254494(v=vs.110).aspx

    Connection String Builders

    https://msdn.microsoft.com/en-us/library/ms254947(v=vs.110).aspx

    Thank you all.

    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Monday, April 4, 2016 3:18 PM

All replies

  • Hello,

     Have you read this thread;

     https://social.msdn.microsoft.com/Forums/vstudio/en-US/440bdb23-c5ee-45df-b873-0b4f8197f9a3/ef-code-first-connection-string?forum=adodotnetentityframework

     There is a link to a walk-thru for EF Code model. Take a look at that also.

    https://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/ms191192.aspx

    https://support.microsoft.com/en-us/kb/316898

    https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/

     Thanks :)

    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Thursday, March 31, 2016 8:18 PM
  • I`m learning EF and I`m searching for examples for applications that Uses CODE FIRST Model.

    Oh, so you are a beginner. And you want to be smacked in the face using the overly complicated Code First a nightmare to say the least about it, And I have seen too many developers windup on the rocks with Code First. :)

    I suggest that you use the DB First model to start with and then make your way to Code First if you choose to go that way, which is much more simpler to use and understand the ins/outs of EF.

    And if using DB First, then the Wizard will ask do you want to encrypt sensitive, like the and psw  info in the connection string for you.

    https://www.simple-talk.com/dotnet/.net-framework/different-approaches-of-entity-framework/

    Thursday, March 31, 2016 8:44 PM
  • Hello,

     Have you read this thread;

     https://social.msdn.microsoft.com/Forums/vstudio/en-US/440bdb23-c5ee-45df-b873-0b4f8197f9a3/ef-code-first-connection-string?forum=adodotnetentityframework

     There is a link to a walk-thru for EF Code model. Take a look at that also.

    https://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    https://msdn.microsoft.com/en-us/library/ms191192.aspx

    https://support.microsoft.com/en-us/kb/316898

    https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/

     Thanks :)

    Hi, and thanks for the links. If I understand your suggestion, you`re suggesting to use CERTs to connect to SQL instead of using a SQL Account Login?

    If I want to use a SQL Login, or a domain account or a local account, how would you store those credentials assuming that they DO NOT exist in the local server? How and where would you store them and only pass them to the connection string when the App starts?

    Thursday, March 31, 2016 10:13 PM
  • I`m learning EF and I`m searching for examples for applications that Uses CODE FIRST Model.

    Oh, so you are a beginner. And you want to be smacked in the face using the overly complicated Code First a nightmare to say the least about it, And I have seen too many developers windup on the rocks with Code First. :)

    I suggest that you use the DB First model to start with and then make your way to Code First if you choose to go that way, which is much more simpler to use and understand the ins/outs of EF.

    And if using DB First, then the Wizard will ask do you want to encrypt sensitive, like the and psw  info in the connection string for you.

    https://www.simple-talk.com/dotnet/.net-framework/different-approaches-of-entity-framework/

    "overly complicated Code First a nightmare" - Really?

    I didn`t went to the other models because e generally don`t like wizards nor lots of extra files and additional code to learn from... But you could be right...

    Can you elaborate on that, what could be or become a nightmare in Code First? What is your bad experiences on this model? 

    Thank you?

    Thursday, March 31, 2016 10:18 PM
  • "overly complicated Code First a nightmare" - Really?

    I looked at Code first briefly about 4 years ago. I didn't liked it and never looked back, because I started out using DB First with EF when there was no other choices but DB First.

    Most developers don't now how to develop databases let alone using an ORM. I have seen too many developers using the Code First approach in the MVC or Web API controllers and gotten themselves burnt and the data hacked because EF is being used too close to the UI.

    Or a developer is trying to use something like a pivot table or other things like Table per Type inheritance etc., etc.,  get trapped and he or she can't get out or the design is so bad using EF Code first by the developer that it affects performance.

    I didn`t went to the other models because e generally don`t like wizards nor lots of extra files and additional code to learn from... But you could be right...

    I don't like Wizards either, but the EF Wiz is great, and I don't have to make 80 classes/objects  the Wiz does it for me and does it right.

    Can you elaborate on that, what could be or become a nightmare in Code First? What is your bad experiences on this model? 

    http://janheggernes.net/post/entity-framework-code-first-or-database-first

    <copy>

    As such, for general development in major projects and/or high performing solutions, I will strongly recommend database first. The developers who may not know how to do anything in SQL shouldn't be tasked with model changes that could potentially hugely affect database performance regardless. Leave that to the people who can and, most importantly, care about the overall health of the model and the performance of the database.

    <end>

    Thursday, March 31, 2016 11:02 PM
  • "overly complicated Code First a nightmare" - Really?

    I looked at Code first briefly about 4 years ago. I didn't liked it and never looked back, because I started out using DB First with EF when there was no other choices but DB First.

    Most developers don't now how to develop databases let alone using an ORM. I have seen too many developers using the Code First approach in the MVC or Web API controllers and gotten themselves burnt and the data hacked because EF is being used too close to the UI.

    Or a developer is trying to use something like a pivot table or other things like Table per Type inheritance etc., etc.,  get trapped and he or she can't get out or the design is so bad using EF Code first by the developer that it affects performance.

    I didn`t went to the other models because e generally don`t like wizards nor lots of extra files and additional code to learn from... But you could be right...

    I don't like Wizards either, but the EF Wiz is great, and I don't have to make 80 classes/objects  the Wiz does it for me and does it right.

    Can you elaborate on that, what could be or become a nightmare in Code First? What is your bad experiences on this model? 

    http://janheggernes.net/post/entity-framework-code-first-or-database-first

    <copy>

    As such, for general development in major projects and/or high performing solutions, I will strongly recommend database first. The developers who may not know how to do anything in SQL shouldn't be tasked with model changes that could potentially hugely affect database performance regardless. Leave that to the people who can and, most importantly, care about the overall health of the model and the performance of the database.

    <end>

    Ok, I understand what you mean.

    I also have DB in place before running the code, and I use 

    Database.SetInitializer<DbContext>(null);

    To prevent That.

    Additionally I use DbModelBuilder to provide custom ORM Rules.

    For now I`m testing, let`s see what the future brings... :)

    Thanks.


    • Edited by JPKI Thursday, March 31, 2016 11:49 PM
    Thursday, March 31, 2016 11:49 PM
  • Hi JPKI,

    I know there's a lot of tutorials out there through out the internet, a good start would be http://www.entityframeworktutorial.net/code-first/what-is-code-first.aspx

    I just want to add some points when choosing code first development.

    1. Code first development doesn’t rely on the entity framework to create EDMX and its mapping. So it might take you a long time to create them especially when you already set-up the database.
    2. It has a complete separation to the database you are working on. In the end, database is just a storage with no logic at all (which is good).
    3. Easy track for version control.
    4. Choosing code first development allows you to have a full control over the code.
    5. Advantageous in database migrations.
    6. You also have to take note that any manual changes to the database will actually screw your code because it defines the database.

    With regards to the authentication, you can use Membership provider. They already handle the roles and permissions + password hashing.

    These stuffs are also good in Web API development.
    You can also look at the following links:

    http://helios.ca/2009/04/22/aspnet-mvc-sqlmembershipprovider/

    https://msdn.microsoft.com/en-us/library/f1kyba5e.aspx

    Best Regards,

    Eric John Adamos


    Failure is not the worst thing in the world.

    The very worst is not to try.

    Email Address : ericjohnadamos@gmail.com

    http://ericjohnadamos.blogspot.com/



    • Edited by Eric John Adamos Friday, April 1, 2016 4:51 AM
    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Friday, April 1, 2016 4:49 AM
  • Ok, thank you all for the links.

    BTW I`m using WPF :)

    Regarding to the Password/User I still don`t have a clear option.

    Let me put this in another way, let`s say I`m creating an application to sell, after the user runs the installer and starts the application for the first time the application should query the user about the SQL server to use and the credentials to connect to the SQL server. The user should supply that information (SQL server name, userId and Password) to the application.

    After that, the application should store that information locally so it can use it each time that the app starts and connects to the Database using the connection string.

    How and where do you store that information after the program has been installed in some sever/workstation?

    Thank you.

    Friday, April 1, 2016 5:56 PM
  • Hi JPKI,

    One question, are these workstation sharing the same information?

    If yes and it is just local workstations, you can have a centralized storage (hosted by a single machine) such as SQL Server, etc. After that, just allow the firewall for that IP address and be sure to reserve that IP address for that machine to make it static. Otherwise, you just install for every machine to have that kind of storage setup. You can either pick SQL database or MS Access.

    Or if world-wide, you can use services such as Azure Database, or dedicated server running SQL Server.


    Failure is not that bad at all, the worst is not even trying. (-,_,-)

    Saturday, April 2, 2016 7:18 AM
  • Hi JPKI,

    One question, are these workstation sharing the same information?

    If yes and it is just local workstations, you can have a centralized storage (hosted by a single machine) such as SQL Server, etc. After that, just allow the firewall for that IP address and be sure to reserve that IP address for that machine to make it static. Otherwise, you just install for every machine to have that kind of storage setup. You can either pick SQL database or MS Access.

    Or if world-wide, you can use services such as Azure Database, or dedicated server running SQL Server.


    Failure is not that bad at all, the worst is not even trying. (-,_,-)

    That` s not my point... My point is to allow the user to provide (while using the App) the credentials to access any given SQL serverName\Instance, and then the App should store those credentials locally to use them in the future each time it starts.

    After that, the application should store that information locally so it can use it each time that the app starts and connects to the Database using the connection string.

    How and where do you store that information after the program has been installed in some sever/workstation?

    Saturday, April 2, 2016 10:10 PM
  • What kind of program is this? Who are the users commercial based or what meaning any user in the world can install this program? Is the program for so company you work for and the program is to run on the company's domain?

    Saturday, April 2, 2016 11:10 PM
  • Ok, I found some good pointers here>

    Connection Strings and Configuration Files

    https://msdn.microsoft.com/en-us/library/ms254494(v=vs.110).aspx

    Connection String Builders

    https://msdn.microsoft.com/en-us/library/ms254947(v=vs.110).aspx

    Thank you all.

    • Marked as answer by JPKI Monday, April 4, 2016 3:19 PM
    Monday, April 4, 2016 3:18 PM