1

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?

  • 1
    windows or sql authentication? – McNets Mar 27 '17 at 21:19
  • 1
    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 – Harvey A. Mar 27 '17 at 21:28
  • 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). – RDFozz Mar 27 '17 at 22:07
  • "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. – Sir Swears-a-lot Mar 28 '17 at 3:18
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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.