How do I switch a SQL Server authentication application to a Windows authentication application?

Question

I developed an application that connects to a database using SQL server authentication. During installation installation, administrator credentials are requested (for SQL server authentication) and the creation of a new user (specific application). Installation requires administrator credentials to create only a specific user. The installer also creates a database and maps this database to a newly created user. Now you need to change the support requirements for authentication in MIXED mode (both for Windows and SQL Server). This means that upon installation, the option to select Windows or SQL mode will be selected. The user can select any of Windows or SQL. If SQL is selected, she will need to provide a username and password. My questions:

1- If the user selects WINDOWS mode, do I need to create a specific application user? If yes, indicate how to create a new user.

2- Throughout the application, I used the connection string with the user name (application user created during installation) and "password". Do I need to create another connection string to accomplish this?

Answer 1

[1] No, you do not need it. You can, and it will work, but it is not required. In Windows authentication, the process will provide credentials for the SQL server of the user who is currently running the code. In most cases, this will be the user who started the process. You can change the user of each thread, if necessary, see Thread.CurrentPrincipal . But I would recommend not to do this, as it greatly complements things.

In most cases, you deploy your application as a service, Windows service, or IIS application. It then runs under specific Windows system accounts that you must allow on the SQL server.

If you do not use Windows system accounts, you will encounter problems with password expiration, reliable password storage, uncontrolled use of a username to run other executable files.

It is trivial to add a user to Windows, just do a search: how to add a user account to Windows [Version Edition], but first use system accounts (for example, Local Service, Network Service).

[2] I would recommend refactoring your application so that you refer to the connection string by name.

---

You want to separate configuration management from the executable. That is, one application prepares the configuration, the other uses it. For example, installer writes the configuration line, the application uses the configuration line and the application does not care about what is inside while it allows access to the database.

Answer 2

You should examine System.Data.SqlClient.ConnectionStringBuilder , and not try to build System.Data.SqlClient.ConnectionString from a string.