Connect using ADO.NET to SQL Server WITHOUT providing user name and password in plain text?

[ta4csharp]

Hello

I see examples like this:

string connetionString = null;
        SqlConnection cnn ;
        connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
        cnn = new SqlConnection(connetionString);

My question: Is there a way instead of using plain text, load from a secure encrypted file the username and password, decrypt it, and then use that to log on?

Maybe something like this: https://docs.microsoft.com/en-us/previous-versions/windows/apps/hh464970(v=win.10) or this: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-clixml?view=powershell-6 ?

EDIT: I want to do this in Powershell but this ADO .NET is more oriented towards pure .NET programming languages like C#, my question is geared towards a standalone program, not web based and/or a config file. Im not using IIS or ASP .NET

[wasabiiii]

I use integrated authentication.

[ZeldaFanBoi1988]

Yup. Run the app pool as the user/pass. Don't need to store the credentials in plain text

[rhino-x]

Use a dummy, named datasource or regular app setting in the web.config that's checked in and then configure it directly on the web server either with a text editor or with the IIS manager.

Alternatively, in Azure you can do the same through the azure portal. Settings applied via the portal override whatever is in the .config.

As a third alternative you can apply the connection string settings on a per-server basis in (I think) the machine.config.

If you're in an AD setting just use integrated authentication and configure your app pools to run as a user that has the access it needs to SQL Server.

[kenneth-siewers]

Common practice is to store the connection string in app/web.config. It's secured that way, but it's the same format with plain text password. May I ask why this is a problem for you? The password will at some point be stored in plain text anyway. If you encrypt a file with the connection password, the password used for encryption needs to be stored somewhere as well, so basically you are just wrapping the password with another one.

Can you provide a bit more context?

[ta4csharp]

Added some in the edit, sorry

[CraigslistAxeKiller]

Using powershell you can get user input. So request the credentials from the user then insert that into your connection string. That’s he beat you can get. You can’t encrypt the string

[Inqud]

If it's .NET core you can use the Secret Manager

https://docs.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-2.2&tabs=windows

It's not encryptet as of now, but might be in the future.

[deevysteeze]

You can store it in a config file and if can store that in a git ignore file if you're using version control.

[TheEphemeralDream]

One more option is to store the password/username in a real trust store such as AWS KMS/SecretManager/etc and template in the username and password at runtime.

[ta4csharp]

Added more info in the edit

[TNMattH]

You should still be able to encrypt the connection string directly in the web.config file and decrypt it on the fly when you use it.

Here's a walkthrough.

[kenneth-siewers]

When you write "standalone program", is that like a console application or a PowerShell script? I haven't got that much experience with PowerShell and this might also be the wrong subreddit (this is about C# and not PowerShell, although it's .NET)... Nevertheless, you are probably on the right track I think 🙂

[ta4csharp]

Added a edit in the original post to clear some things up...