Why audit table needed to be created all of a sudden?


(Alexander) #1

We had some patches installed over the weekends and all of a sudden an endpoint that was running for a while could not start. It was trying to create an audit table and did not have permissions to do that. Why would all of a sudden it needed the table if it was running just fine before?


(Andreas Öhlund) #2

What transport are you using?

Did you upgrade any of the NServiceBus packages?

Are you using the NServiceBus installers?

Note that we don’t recommend to use them on production servers but rather use scripts to create infrastructure assets like queues

https://docs.particular.net/nservicebus/operations/installers#running-installers


(Alexander) #3

We use rabbitmq transport
No upgrades were done to the service. Only patches were applied to the server OS.
We do use NSB installers.
The installers were never an issue for us. Even this time, if the account had rights, it’d create a table without us knowing. The question is why wasn’t the table created/needed before and all of a sudden the endpoint decided to create it.


(Sean Feldman) #4

RabbitMQ would use an audit queue, not a table. Is that what you mean?
In case it’s something else, could you please elaborate? Configuration code (w/o sensitive information), versions of the packages used, and RabbitMQ info would help.


(Alexander) #5

Correction, the endpoint uses two transports - SQL and RabbitMQ.
The fact that the endpoint also uses SQL transport, explains why it tries to create an audit table. It does not explain why now though.

RabbitMQ version is 3.3.5.

Package file extract showing NSB components:

package id=“NServiceBus” version=“5.2.5” targetFramework="net45"
package id=“NServiceBus.RabbitMQ” version=“2.1.3” targetFramework="net45"
package id=“NServiceBus.SqlServer” version=“2.1.4” targetFramework="net45"
package id=“RabbitMQ.Client” version=“3.5.4” targetFramework="net45"
package id=“ServiceControl.Plugin.Nsb5.Heartbeat” version=“2.0.0” targetFramework=“net45”

SQL Transport Configuration:

        private IBus StartErdbBus()
        {
            BusConfiguration configuration = new BusConfiguration();

            configuration.UseTransport<SqlServerTransport>()
                .DefaultSchema("Nsb")
                .DisableCallbackReceiver()
                .ConnectionString(AppSettings.SqlConnectionString);
            configuration.EndpointName("NsbIntegration");
            configuration.UseSerialization<JsonSerializer>();
            configuration.UsePersistence<InMemoryPersistence>();
            configuration.EnableInstallers();
            configuration.Transactions().DisableDistributedTransactions();
            configuration.DefineCriticalErrorAction(ErdbBusOnCriticalError);

            IExcludesBuilder excludesBuilder = AllAssemblies.Except("ServiceControl.Plugin.Nsb5.Heartbeat");
            configuration.AssembliesToScan(excludesBuilder);

            return Bus.Create(configuration).Start();
        }

Rabbit Transport Configuration:

        private IBus StartRabbitBus()
        {
            BusConfiguration configuration = new BusConfiguration();

            var rabbitConnectionString = new Halp.Common.RemoteConnectionString("NServiceBus/RMQTransport");

            configuration.UseTransport<RabbitMQTransport>()
                .DisableCallbackReceiver()
                .ConnectionString("****");
            configuration.EndpointName("Halp.DomResearch.Erdb.Endpoint");
            configuration.UsePersistence<InMemoryPersistence>();
            configuration.EnableInstallers();
            configuration.Transactions().DisableDistributedTransactions();
            configuration.DefineCriticalErrorAction(RabbitBusOnCriticalError);
            return Bus.Create(configuration).Start();
        }

Extract from the app.config is attached: App.zip (920 Bytes)


(Andreas Öhlund) #6

Is there an Audit table in the database?

Audits are on by default and since you have installers enabled the endpoint should always see if there is an audit table and create one if not present.

How did you notice this btw? Did you get an error?


(Andreas Öhlund) #7

Note that we don’t recommend to use them on production servers but rather use scripts to create infrastructure assets like queues

Just to clarify, we recommend this for the transports and persistence that provide scripts for setting up infrastructure (Msmq, SqlTransport, SqlPersistence). Rabbit doesn’t yet have this so running the installers is the way to go there.


(Alexander) #8

The windows service hosting the endpoint could not start in production. The reason was that the service account did not have permissions to create the audit table. Apparently, the audit table was not in the DB - at least as far back as the DBAs could check. Thus the questions: why wasn’t it created before and why all of a sudden the endpoint was trying to create it now.


(Andreas Öhlund) #9

Did you make any config changes in the endpoint or did you upgrade any packages?

Or did this happen by the service just restarting?


(Alexander) #10

It happened when the service tried to start or, to be precise,
the server was restarted after applying patches. The service started automatically on the server start.


(Andreas Öhlund) #11

In that case I’m clueless, the endpoint must somehow have detected that the table wasn’t there.

Does it work now?

I would suggest that your turn off installers for the SqlTransport endpoint in production unless you need them for something else and instead use scripts when you provision new endpoints

https://docs.particular.net/transports/sql/operations-scripting#create-queues


(Marqus Landin) #12

Hi Andreas, we are running AzureServiceBusTransport, Outbox and SqlPersistence.

configuration.EnableInstallers();
configuration.UsePersistence();
var persistence = configuration.UsePersistence<SqlPersistence, StorageType.Outbox>();
persistence.SubscriptionSettings().DisableCache();

Just to calrify, I guess I don’t need the persistence.SubscriptionSettings().DisableCache(); since we don’t store the Subscriptions in db.

Since we are just using Outbox, is it still recommended to not use the configuration.EnableInstallers(); in Production?

Regards
Marqus


(Andreas Öhlund) #13

I guess I don’t need the persistence.SubscriptionSettings().DisableCache()

Correct

Since we are just using Outbox, is it still recommended to not use the configuration.EnableInstallers(); in Production?

You shouldn’t need them, instead execute the outbox creation script that is created when you build the project.

See https://docs.particular.net/persistence/sql/installer-workflow?version=sqlpersistence_4#contrasting-workflows-higher-environment-workflow for more details

Regarding the transport you should be able to create scripts to create queues for new endpoints using https://docs.particular.net/transports/azure-service-bus/operational-scripting

Should you need the installers for other things still you can always disable them for SqlPersistence only using:

https://docs.particular.net/persistence/sql/installer-workflow?version=sqlpersistence_4#contrasting-workflows

Hope this makes sense!