CODEWITHSUNDEEP
c#user-interfaceasp.net-mvc-5database-connection
Kevin Francis
Kevin Francis

Reputation: 385

How to create an end user SQL Server connection dialog using MVC/C#

I'm new to C# and MVC. Trying to create a intranet application that allows the user to configure or change the initial SQL Server connection string. The connection will need to support SQL Mixed Mode authentication. While my back end skills are exceptional, this is my first attempt at creating a web based intranet web application. I've been searching Google for 3 days trying to find an example, tutorial or documentation with no luck. I've reached a point where I'm not even sure if this is an accepted practice. I'm using VS2015, SQL Server 2012, C#, MVC, ASP.Net and targeting the .Net Framework 4.61. Any guidance is appreciated.

Upvotes: 0

Views: 830

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

Probably you can't easily change default/initial connection string in web.config dynamically (and considered harmful to change it directly on web.config), however SqlConnectionStringBuilder can build your connection string by request in run-time:

// assume these properties are part of DB provider definition
public class DatabaseSettings 
{
    public String ConnectionName { get; set; }
    public String ServerName { get; set; }
    public String DatabaseName { get; set; }
    public String UserId { get; set; }
    public String Password { get; set; }
}

// controller method definition
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

builder.DataSource = DatabaseSettings.ServerName;
builder.InitialCatalog = DatabaseSettings.DatabaseName;
builder.IntegratedSecurity = true;
builder.UserID = DatabaseSettings.UserId;
builder.Password = DatabaseSettings.Password;
builder.MultipleActiveResultSets = true;

// modify initial connection string in runtime
// note that ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString is readonly by default, 
// thus use reflection to disable private bReadOnly field before adding custom connection string
var connectionString = ConfigurationManager.ConnectionStrings;
var collection = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
collection.SetValue(connectionString, false);

// This one may work if you tend to change default connection string value
// connectionString[0].ConnectionString = builder.ToString();

// Safer way by adding new name rather than replace default one
connectionString.Add(new ConnectionStringSettings(DatabaseSettings.ConnectionName, builder.ToString()));

AFAIK you can store user-defined connection string inside XML file for each user and load programmatically when someone requires it (see Reading connection string from external config file).

Regarding mixed mode (Windows & Forms authentication for both intranet & internet), setup your IIS to 2 entrypoints (i.e. virtual directories), where complete app should rely on Forms authentication mode. The controller of Windows authentication simply redirect to main site by passing user identity.

On your main site's login page, you may want to add "Login with Windows/Active Directory account" button (similar to login button with existing social media accounts) and clicking it will redirect intranet users to a controller specifically crafted to receive credentials from Windows authentication mode.

Perhaps this is not the best way to answer your needs, but at least may open your mind to learn and figure what things to do.

References:

1) Change connection string programmatically

http://david.gardiner.net.au/2008/09/programmatically-setting.html

2) Dual-mode authentication

https://msdn.microsoft.com/en-us/library/bb669066(v=vs.110).aspx (MS SQL Server authentication article)

http://www.codeguru.com/csharp/.net/net_security/authentication/article.php/c19559/ASPNET-Mixed-Mode-Authentication.htm

http://mvolo.com/iis-70-twolevel-authentication-with-forms-authentication-and-windows-authentication/

Upvotes: 1

Related Questions