6

I have an app service in Azure operating as an API for a system I'm designing. As the API is responsible for accessing the database directly, I obviously don't want to be storing connection strings in the source code, so have stored it in the Connection Strings section within the App Service's Configuration on the Azure dashboard.

My code is pretty much a carbon copy of this >> https://github.com/medhatelmasry/JwtAuthentication/blob/master/JwtAuthentication/Startup.cs, except I have a check for the current configuration it's running in (debug, release, etc) so that when I'm debugging locally in Visual Studio I am using a localdb connection (hard coded). I have an appsettings.json file but there are no connection strings in it, only settings for JWT authentication and logging.

When this is being called:

services.AddDbContext<ApplicationDbContext>(
            option => option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

I'm getting the following in Azure:

Unhandled Exception: System.ArgumentNullException: Value cannot be null
Parameter name: connectionString

I've been working stupid hours over the past week trying to get this working and been going in circles, I'm driving myself to insanity. Google and StackOverflow results have been mixed as there are different answers from different versions of Azure and ASP.NET Core over the years. It's like it can't access the Azure configuration at all. Please refer to the link above as this is the same setup as I have, and there have been many different answers based on .NET versions and types (core or framework).

Edit: Please read my question, the connection string is not stored in the project's appsettings.json file, it's stored in Azure, as below (I've blanked the connection string names, but they do match what's in the code, and no it's not "DefaultConnection"):

Azure Connection Strings

ataraxia
  • 995
  • 13
  • 31
  • Configuration.GetConnectionString() would require JSON like this: `{ "ConnectionStrings": { "DefaultConnection": "DB Connection String here" } }`, how does your settings file look like? – juunas Aug 22 '19 at 10:19
  • 2
    Please read my question again, the connection string is not stored in the settings file, it's stored in Azure, as per Microsoft's guidance. Screenshot added. – ataraxia Aug 22 '19 at 10:26
  • Review the following https://stackoverflow.com/a/48959565/5233410 – Nkosi Aug 22 '19 at 10:33
  • The environment variable configuration provider should get those... https://github.com/aspnet/Extensions/blob/master/src/Configuration/Config.EnvironmentVariables/src/EnvironmentVariablesConfigurationProvider.cs – juunas Aug 22 '19 at 11:18
  • @Nkosi that seems like it may fix it, will try when I get home from work – ataraxia Aug 22 '19 at 11:44
  • @juunas I think the issue with that is that I'm not explicitly adding environment variables in my code as I'm relying on whatever's calling Startup to provide an IConfiguration that already contains this data, which it clearly doesn't. – ataraxia Aug 22 '19 at 11:45
  • If you are not creating the config builder and using the default Web host builder, environment variables should be there – juunas Aug 22 '19 at 11:56
  • @juunas they're not, or rather I presume they're not, as Azure is throwing a null reference exception when trying to find one. I think Nkosi's answer may sort this, I think it may be a version issue, need to wait until I get home from work. – ataraxia Aug 22 '19 at 11:59
  • 1
    You can view the environment variables of the app service in the Development Tools -> Advanced Tools section. That should prove whether or not the environment variable exists. It should be in the format of ConnectionStrings__DefaultConnection or ConnectionStrings:DefaultConnection (Windows app service only) – David C Aug 22 '19 at 12:57
  • Further note, you're still exposing the connection string to anyone who has access to the azure portal. A step further would be to use Managed Identity and have the connection string in KeyVault with even greater security around it. – David C Aug 22 '19 at 12:58
  • @DavidC799 First comment, I will have a look at that also. Second comment, I will be looking to used Managed Identity instead once I can get this crap out of the way. At the moment only I have access to the portal. Surely though if somebody has access to the portal they can just as well mess with Managed Identities? – ataraxia Aug 22 '19 at 13:04
  • @DavidC799 I'm gonna guess that no it isn't in there? https://imgur.com/a/VxlpOe5 – ataraxia Aug 22 '19 at 13:09
  • Are you in https://YOURSITE.scm.azurewebsites.net/Env.cshtml ? You should see quite a number of app settings – David C Aug 22 '19 at 13:16
  • @DavidC799 Now I am, and I think I see the issue. My connection string is there but a) It's had hyphens removed and b) It's prefixed with "SQLCONNSTR_" then the name. So, where I've set it in the portal as "Here-Is-My-SQL-Name", the actual setting in the app service is "SQLCONNSTR_HereIsMySQLName". Will test later. If this works you may want to add this as the answer so I can accept it, as it is this guidance that's led me to finding the **actual** stored values. – ataraxia Aug 22 '19 at 13:23
  • No worries, good luck! – David C Aug 22 '19 at 13:23
  • 1
    @DavidC799 you are an absolute DIAMOND!!!! It was the naming thing, if you put your answer I will accept it and give a few more details, I'm going to bed! – ataraxia Aug 23 '19 at 00:00

2 Answers2

4

Make sure the naming of your variables are valid. You can view the actual environment variables from the SCM site at Development Tools -> Advanced Tools when in the App Service blade.

A connection string added via the portal Configuration section of the App Service with the name of "DefaultConnection" will look like SQLCONNSTR_DefaultConnection in the environment variables. To access it in code then, you would do configuration.GetConnectionString("DefaultConnection")

See this Microsoft blog for details on setting up the app service config values.

David C
  • 664
  • 1
  • 8
  • 21
  • This was the issue. My connection string had hyphens which seem to be not allowed (I'll presume anything non-alphanumeric isn't allowed). Say my connection string was "The-SQL-Server", the actual variable that was saved by Azure was "SQLCONNSTR_TheSQLServer", however "SQLCONNSTR_" is read and stripped out by Azure, so to call this from code you would use `Configuration.GetConnectionString("TheSQLServer")`. – ataraxia Aug 23 '19 at 13:47
  • It's taken a very long time to figure this out and in all the Microsoft documentation I've read, there is no mention of disallowed characters for Azure variable names. – ataraxia Aug 23 '19 at 13:48
  • Yea I had a look through for restricted characters but couldn't find anything either. You're allowed colons and underscores for sure, anything else I would actively avoid! – David C Aug 23 '19 at 15:59
  • It's poor, very poor. I can see my comment got edited, probably for the language, but I have been working stupid hours this week to get to the bottom of this, and am NOT happy with Microsoft at all, especially as Azure has actually stripped out what I've entered on the configuration itself but **still kept the same name in the dashboard**. To anybody reading this I'd say play it safe and just stick to alphanumeric with camel casing if needed (which is what I've done). Just got to fix the Managed Identity connection string that's broke now! *dies inside a little* – ataraxia Aug 23 '19 at 16:37
  • You doing it via KeyVault now then? – David C Aug 23 '19 at 18:54
  • I don't know what I'm doing, searching the various exception messages and logs I'm getting then yes I think I do need to be using KeyVault. The documentation and help on forums is very stagnated, guidance is not clear at all :( – ataraxia Aug 23 '19 at 23:10
  • As a follow-up to this, I am now using a system-assigned identity to access the db, so the username and password are no longer stored in the connection string. I had an initial problem of access being denied, but both the original setup and the fix can be found here >> https://stackoverflow.com/questions/57650568/azure-app-service-cant-access-sql-server-login-failed-for-user-nt-authority. I'll produce a full guide on doing this all in the portal when I get chance. – ataraxia Sep 04 '19 at 11:57
  • Very nice, I didn't know you could use the System Identity to access the DB directly! The way I was talking about was you'd have your connection string in KeyVault, and the App Service would have direct connection via the System Identity to it. – David C Sep 04 '19 at 21:40
  • Yeah I looked into the KeyVault and also thought that's how I would have to do it, which I wasn't looking forward to! The system-managed identity is much neater and also, as per my post, can be set specifically to a database rather than the whole db server due to having to provision a user on the database. – ataraxia Sep 05 '19 at 10:04
1

I know its an old one, but might be some use to someone in the future. My issue was caused by having the connection string in the AppService 'Connection Strings'. By putting it as a value in the 'Application Settings' instead it worked fine. I'm guessing because of the encryption that applies to the Connection String values.

DMur
  • 625
  • 13
  • 26