Failed db login exception on db creation

[alexsrdev]

Bug Report / Support Request Template

If you are opening a feature request, you can ignore this template. Bug reports and requests for assistance usually require the same basic information described below. This will help us more quickly reproduce and investigate the problem you're reporting. (If you are using Serilog.Sinks.MSSqlServerCore, that package is deprecated, please switch to Serilog.Sinks.MSSqlServer before reporting an issue.)

Please clearly describe what the SQL Sink is doing incorrectly:
When trying to create the mssql db using package manager console executing a update-database, the mssql sink generates a login exception.

Please clearly describe the expected behavior:

List the names and versions of all Serilog packages used in the project:

• Serilog: 8.0.1 (Serilog.AspNetCore)
• Serilog.Sinks.MSSqlServer: This happens when using a version higher than 6.3.0.
• (configuration, etc.)

Target framework and operating system:

[x] .NET 8
OS: Running on top of WSL and dotnet/aspnet image

Provide a simple reproduction of your Serilog configuration code:
"Serilog": {
"Using": [ "Serilog.Sinks.Console" ],
"EnableUi": true,
"MinimumLevel": "Information",
"WriteTo": [
{
"Name": "Console",
"Args": {
"outputTemplate": "[{Timestamp:u} {Level:u3}] {Message:lj}{NewLine}{Exception}"
}
},
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "DefaultConnection",
"sinkOptionsSection": {
"tableName": "ServiceLogs",
"schemaName": "dbo",
"autoCreateSqlTable": true
},
"restrictedToMinimumLevel": "Information"
}
}
]
},

Provide a simple reproduction of your Serilog configuration file, if any:

Provide a simple reproduction of your application code:
Line throwing the exception:
builder.Host.UseSerilog((ctx, lc) =>
{
lc.ReadFrom.Configuration(ctx.Configuration);
});

I noticed that since mssqk sink version 6.4.0, the sql executor is re-throwing the exception (see here):
bf1f5e6
bf1f5e6#diff-f8c67af6a71f2771abeac7d9eb21caee190c7f6530085394bd37f24c60c9e65a

Not sure why the change, but this sink should not be trying to connect to database on a db migration execution (e.g. update-database).

When I downgrade to 6.3.0, everything works as expected (db creation is successful).

[C-Coretex]

Same for me with Serilog.Sinks.MSSqlServer version 6.6.0
A downgrade to 6.3.0 helped

[ckadluba]

Hi @alexsrdev and @C-Coretex!

I'll take a look at this as soon as I can. Thanks for letting us know.

Christian

[rmacfadyen]

This issue also impacted me for a net8 API project.

It was particularly tricky to track down because in our environment the database is only missing during certain test stages via docker-compose (spins up new/fresh/empty sql server and spins up the API who's efcore migrations then create the database... or in this case the Startup.cs crashes in builder.Build()).

A lot of effort to track this down. And only tracked down by detailed examination of the stack trace and noticing that Serilog was in the middle of it.

Workarounds:

• Do not upgrade past 6.3.0
• Change where you call EFCore migrations from to happen before the "builder.Build()" call

[rmacfadyen]

Note that the autoCreateSqlDatabase flag may require access to the "master" database, regardless of whether or not the logging database exists or not. This fails on Azure SQL (the master database is not user accessible).

So... not much of a work around if your app/api actual deployment uses Azure SQL,

Here's the error you get for Azure SQL when autoCreateSqlDatabase is true:

[hostname]-6686d6cf7f-fh9c4  ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The server principal "[hostname]" is not able to access the database "master" under the current security context.
[hostname]-6686d6cf7f-fh9c4 Cannot open user default database. Login failed.

[ckadluba]

Hi!

Thank you for reporting this. Please be aware that the auto create log table and log database features were intended for convenient local testing and development purposes. Those features are not recommended for production deployments. I think we should state this clearly in the documentation.

The rethrow of exceptions in SQL executor class was added because when using the audit sink (AuditTo()) the calling code needs to have a way of knowing when a log attempt failed and be able to handle this situation. The batched sink (WriteTo()) does not rethrow such exceptions because it writes log events delayed in the background and not syncronously like audit.

Regarding the access to master db in Azure SQL, I think you are just lacking the correct permissions for the identity which the SQL sink is running with. According to this documentation, access to master db is possible in Azure SQL https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql#create-additional-logins-and-users-having-administrative-permissions.

Since some of you might experience this issue, can someone please share a complete but simple sample application which we can use to reproduce the problem (using a local SQL instance)?

Thank you.

Christian

[ckadluba]

I will close this issue soon due to inactivity and since we could not reproduce it yet.

[alexsrdev]

@ckadluba : This happens when you ask the mssql sink to create the log table if doesn't exist and the db hasn't being created yet.

E.g (appsettings approach):

"Serilog": {
    "Using":  [ "Serilog.Sinks.Console", "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": "Debug",
    "WriteTo": [
      { "Name": "Console" },
      { 
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=localhost,1433;Database=myDataBase;User=sa;Password=Password1234;TrustServerCertificate=true",
          "tableName": "Logs",
          "autoCreateSqlTable": true
        }
      }
    ]
  }

If I execute this "dotnet ef database update" command using the terminal, the console receive the error, but it continues and creates the db. Now, when I try to use Visual Studio Package Manager console, the error halt the command execution and it doesn't creates the db.

This is the behavior starting at 6.4.0 and later from any type of project so you can tried from your own.

[ckadluba]

I have no sample with EF core and MSSQL sink and also no bandwidth to create one right now. If you want to help us to investigating this, please send a complete and simple sample program that we can run to duplicate your issue. It should be easy for you to provide that.

[alexsrdev]

Sure, no problem. serilog.mssqlsink.test