0

It may be really simple question, but at the moment I have a kind of mental loop and need your help to break it. SQL server 2016 is installed using a domain account. I didn't create an explicit login, there is only a standard generic NT SERVICE\MSSQL$XXX login and all SQL Agent Jobs like backup, rebuild indices and dbcc checkdb are running with no problems, but if I create an SQL job running a simple select on the user database like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 I get an error

Executed as user: [active directory account of the SQL Service]. 
Failed to initialize sqlcmd library with error number -2147467259. 
[SQLSTATE 42000] (Error 22050).  The step failed.

I have to create a login for SQL service domain account to fix an error.

Any ideas what happens were? Is this a security feature of the SQL server?

UPD: after a drill-down I've found an answer about sqlcmd error: it caused by msdb.dbo.sp_send_dbmail with @query parameter if the query in @query fails. But I do not have an answer to my main question: why I can't run a query over the user database using an SQL server service account.

UPD2: UPD2: as I realized my description is not that understandable, sorry for that. One more try. An SQL server is installed to run using active directory accout A. That means, it I open services.msc I see that account as a login account of the service. This configuration was implemented via configuration file, not as an after-setup change. SQL Server logins doesn't contain account A, only generic NT SERVICE\MSSQL$XXX and NT SERVICE\SQLAgent$XXX which are a members of sysadmin role as usual. All SQL maintenance jobs (index maintenance, DBCC and backups) are configured using Ola's scripts and running without any problems also on user databases. If I try to create an SQL Server Job to query user database even if it's a simple query like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 the query can't run. SQL server trace shows an error: "The server principal A is not able to access the database "AdventureWorks2016" under the current security context" If I create a login A and add it to the sysadm role the job runs, no problems, but it's absolutely clear, as a sysadm it should not have problems to query a database. The point I do not understand: all other SQL jobs are running using generic account in the background, so there is a kind of impersonification in SQL: accout A is using a generic login. As an additional information: if I create a Login for A I can't delete it because SQL server means, User is logged in, so SQl user uses it in the backupground to connect to the SQL and to impersonificate a generic login. Why it's not possible if I query a user database via SQL Agent Job?

DrillDown 2nd:

  USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 19.01.2022 11:21:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test_query_userdb_2Steps', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [query separate]    Script Date: 19.01.2022 11:21:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query separate', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select * from dbo.Users where 1=2', 
        @database_name=N'StackOverflow', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [query]    Script Date: 19.01.2022 11:21:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query param', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name=''my profile'',
@recipients=''my email'',
@subject=''ALERT: XX'',
@query =''select * from dbo.users where 1 = 2''', 
        @database_name=N'StackOverflow', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The same query runs as a 1st step of the job and crashes the job if it runs as a @query parameter of send_mail SP. There is no permission context change between steps.

9
  • 3
    Try something more generic like SELECT @@SERVERNAME, which should eliminate any permissions issues as a consideration. Jan 13 at 13:05
  • @JonathanFite, select @@servername has no problems to run. Yes, I know it's a permission issue, but I do not understand why: active directory account has sa permissons via generic account NT SERVICE\MSSQL$XXX, all maintenance jobs are running using this context, but it can't start a query on the use database: The server principal " active directory account" is not able to access the database "AdventureWorks2016" under the current security context. That's why I speak about a mental loop: there is something I oversee. Jan 13 at 15:39
  • Can you add more details? The error is strange. Is refers to sqlcmd, but it also seems like a tsql jobstep. That doesnt add up. Jan 13 at 17:16
  • 1
    This is all too confusing to me. Some of the things that confuses me: By "sa permissions", do you mean member of the sysadmin role? Also "Active directory account has sa permissions via generic account NT SERVICE\MSSQL$XXX"; that doesn't make sense. NT SERVICE\MSSQL$XXX is a local account that doesn't exist in AD. Also, is database mail involved or not? Or are there two separate issues. What are the minimum repro steps? If those doesn't involve Db Mail, then keep that to a different post. Jan 17 at 9:58
  • 1
    I suggest you put yourself in our shoes, knowing nothing of your setup and re-phrase your questions as clearly and distinct as possible. Include exactly what you do, and try to phrase it in a way to se can try to repro your steps. ...Or it is just me being dense... :-) Jan 17 at 10:00

1 Answer 1

0

Answering Your Questions

Any ideas what happens were?

Possibly....

Is this a security feature of the SQL server?

No, not quite.

What happens when you create a job, is the job is created in the context of the user creating the job. So if you log in to the SQL Server instance with your DOMAIN\USER account and create a job, then that job will be created with DOMAIN\USER as the job owner. When it executes it will do so in the context of the job owner. This means that if the job owner does not have the permission to access the database via a database user, then the job will fail. This is what you might be observing.

SQL Server - New SQL Server Agent Job - Properties displaying Job Owner DOMAIN\USER

If you want the job to run, then you are going to have to assign that user with certain database permissions.

You can do this in the SQL Server Login - Properties. Navigate in your SQL Server instance down the tree and find the login you want to modify:

  • INSTANCE (SQL Server xx.xxx.xxxx - ..) |
    • Security |
      • Logins

The SQL Server Login will look a bit like this:

SQL Server Login

Now navigate to the User Mapping section:

SQL Server Login - User Mapping

Here you select the Database you wish to be able to read from, which will automatically create the same DOMAIN\USER Database user for you. Assign a default schema if required (normally dbo) and then in the lower portion select the generic database role db_datareader.

Close all windows with OK.

Now you have a Database User that matches your SQL Server Login (no, they are not the same) and you should be able to select from your desired database.

Answering Another Of Your Unspoken Questions

Why do the maintenance jobs run?

The jobs you run for backups, etc, are normally executed in the context of the service accounts and they are normally set to have the SQL Server Role sysadmin.

A sysadmin (An account assigned to the SQL Server Role 'sysadmin`) can performn nearly anything on a SQL Server instance.

SQL Server Roles can be found in the SQL Server Login - Server Roles section of the SQL Server Login.

Summary

  • SQL Server Logins are allowed to connect to the SQL Server instance.
  • Database Users are allowed to perform certain actions against a database.
  • SQL Server Logins are linked to Database Users (normally) to GRANT ... specific permissions.
  • SQL Server Service Accounts are automatically created as sysadmin which doesn't require them to be assigned to specific Database Users. So they can access any database and perform any maintenance task. This is not recommended for normal Users and/or Applicaitons accessing databases.
  • SQL Server Logins can be named differently than their corresponding Database User.
  • When you create a SQL Server Agent Job, it is normally created in the context of the current User Account, be it a DOMAIN\USER or a SQL Server Login (e.g. SomeSQLUser). This account must have access to the database it will be interacting with.
2
  • Thank you for a kind explanation, unfortunately it seems to be not that easy. Owner of the job is sa. SA login is not deactivated and also not a "dummy account". RunAs in a job step is not configured. To make it much more confusing I tried it on the server which is running using generic account, not AD one and created a job containing 2 steps running exact the same query: 1. query as is --> running im SQL Agent context with no problems, 2. query as @query parameter of msdb.dbo.sp_send_dbmail crashes the job because of security context as mentioned in my question. Jan 19 at 7:34
  • I'll digest your information and see if I can add to my answer. Could you script out the job and post that in your question? Remove all sensitive Information first.
    – John K. N.
    Jan 19 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.