Connecting to SQL Server database from a .net application

Question

I need to add a new form to an application which requires access to SQL Server to retrieve data from a single view and display it to the user. This application is an enterprise level application with ~1000 users.

I assume the best practice is to ask DBA to provide a system account with non-expiring password so I can use it to connect to the SQL Server.

Is there any other alternative to using SQL Database account?

How should I write the connection string in my code?

• McNets: windows or sql authentication?
• Harvey A.: In order to use Windows Authentication all users need to have access to the DB, which I don't think it's the best practice
• RDFozz: That depends. If all users' access to the database is appropriately controlled (for example, if they can't manage to run ad hoc SQL that drops tables), then most organizations would prefer to provide access that can be tracked back to a specific individual. If your view is in its own schema, and users aren't granted access to the schema with the actual tables, I would think it would be workable. Of course, flip side, a SQL login that can't make any changes would be equally secure (unless access to the data itself should be highly restricted).
• Sujana Chowdary: See Using Entity Framework With an Existing Database: Data Access by Jon Smith.
• Sir Swears-a-lot: "I need to add..." To an existing application? How does the application handle security now? I would recommend that you follow the conventions already in place.

Answer 1

There are different ways to connect to Database.

1. ADO.NET

using System;
using System.Data;
using System.Data.OleDb;
class Sample
{
 public static void Main() 
 {
 OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
 OleDbCommand catCMD = nwindConn.CreateCommand();
 catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";
 nwindConn.Open();
 OleDbDataReader myReader = catCMD.ExecuteReader();
 while (myReader.Read())
 {
 Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
 }
 myReader.Close();
 nwindConn.Close();
 }
}

2. Web.Config Connection String

 <connectionStrings>
 <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
 </connectionStrings>

3. Connect to a SQL Database and Use the LINQ to SQL Designer

This process involves multiple thing, you can read more here

https://blogs.msdn.microsoft.com/charlie/2007/11/19/connect-to-a-sql-database-and-use-the-linq-to-sql-designer/