Change Notifcation service error with Windows Authentication

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
bryn.ball
Posts: 9
Joined: Mon 08 Dec 2014 03:32

Change Notifcation service error with Windows Authentication

Post by bryn.ball » Wed 20 Mar 2019 06:03

Hi there

I want to change my application to use Windows Authentication in our server environment but when the ChangeNotification component attempts to create the service I am getting the error "The default schema does not exist. Code: (2797)"

I have traced it to this statement using SQL Server Profiler

IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = 'SDAC_NS_53_QUEUE') CREATE QUEUE SDAC_NS_53_QUEUE
IF NOT EXISTS (SELECT * FROM sys.services WHERE name = 'SDAC_NS_53') CREATE SERVICE SDAC_NS_53 ON QUEUE SDAC_NS_53_QUEUE([http://schemas.microsoft.com/SQL/Notifi ... tification])

Can you please advise what SQL Server security settings I need to permit for the change notification service to be loaded?

Thanks
Bryn

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Change Notifcation service error with Windows Authentication

Post by Stellar » Thu 28 Mar 2019 09:02

Please check which default schema is set for the user. For this, execute the following SQL expression:

Code: Select all

SELECT name, default_schema_name FROM sys.database_principals 
If the schema is not specified for the user, or schema is incorrect, please try modifying the default schema for the user. For example:

Code: Select all

ALTER USER [Domain_Name\User_Name] WITH DEFAULT_SCHEMA = dbo;

bryn.ball
Posts: 9
Joined: Mon 08 Dec 2014 03:32

Re: Change Notifcation service error with Windows Authentication

Post by bryn.ball » Thu 28 Mar 2019 19:27

Thanks for your reply.

I have done a lot of investigation and learning over the last few days. I discovered the fact that somehow the owner of the default schema had changed, have put it right now and all appears ok.

I really appreciate your assistance. These are awesome components and I thoroughly enjoy working with them!!!

Thanks and kind regards

Bryn

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Change Notifcation service error with Windows Authentication

Post by Stellar » Fri 29 Mar 2019 09:38

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

Post Reply