When working with Power BI Reports, you want dashboards and reports to be regularly updated with data sources. The Interactive Reports and Data Visualizations should reflect current information and charts.
Symptom
Updating the reports is performed by the Power BI Report Server scheduler. You get the SQL Server Error 15404 during a SQL Server On-Prem installation. Log File Viewer can found in the SQL Server Management Studio – SQL Server Agent – Log File Viewer.
Cause
The upgrade tasks scheduled by Microsoft SQL Server Reporting Services (SSRS) and Power BI Report Server (PBRS) are performed by SQL Server Agent. SQL Server Agent is a Windows service that performs scheduled management tasks (jobs). For permission to perform the update task to be fulfilled, authentication of an Active Directory domain account is required. The Microsoft SQL Server installation involves running the SQL Server Agent service as NT SERVICE\SQLSERVERAGENT thereby Active Directory is refusing access to SQL Agent.
Solution
Use an Active Directory domain user account to log on.
SQL Server Configuration Manager
Change SQL Server Agent authentication with ran the services.msc MMC-console to enable SQL Server Agent service through Properties – Log on, or use the SQL Server Configuration Manager.
What is Power BI Report Server
Power BI Report Server is an on-premises report server with a web portal in which you display and manage reports and KPIs. Along with it come the tools to create BI reports, paginated reports, mobile reports, and KPIs. Your users can access those reports in different ways: viewing them in a web browser or mobile device, or as an email in their in-box.
PBI Report Server is similar to both SQL Server Reporting Services and it’s online service, but in different ways. Like the PBI service, PBI Report Server hosts PBI reports (.pbix), Excel files, and paginated reports.
Your SQL Linux has been joined to domain and you can connect to the SQL Server instance using Windows Authentication.
The connection itself is fine, but when you run some high privilege T-SQL statements like ‘Create Login’ , ‘sp_addsrvrolemember’ , you may run into the issue ‘Error 15404 ‘Could not obtain information about Windows NT group/user ‘%ls’, error code 0x80090304”
You will find following messages in PALLOG
11/22/2019 13:56:26.448761588 Debug [security.kerberos] <0000040947/0x00000200> Processing SSPI operation 0x0000000F
11/22/2019 13:56:26.449366379 Error [security.ldap] <0000040947/0x00000200> Initializing credentials for use in new cache failed: Keytab contains no suitable keys for red4$@SQLREPRO.EDU
11/22/2019 13:56:26.449613575 Debug [security.kerberos] <0000040947/0x00000200> Import name [ADMINISTRATOR@SQLREPRO.EDU] returned [ADMINISTRATOR@SQLREPRO.EDU]
11/22/2019 13:56:26.449633375 Debug [security.kerberos] <0000040947/0x00000200> Import name [red4$] returned [red4$]
11/22/2019 13:56:26.449753473 Debug [security.kerberos] <0000040947/0x00000200> Import name [RED4$] returned [RED4$]
11/22/2019 13:56:26.449905471 Debug [security.kerberos] <0000040947/0x00000200> Import name [red4$] returned [red4$]
11/22/2019 13:56:26.450014469 Error [security.kerberos] <0000040947/0x00000200> GSS MAJOR: 851968 GSS MINOR: 39756033 Error acquiring credentials in AcquireCredCaseInsensitive
11/22/2019 13:56:26.450029069 Error [security.kerberos] <0000040947/0x00000200> Unspecified GSS failure. Minor code may provide more information
11/22/2019 13:56:26.450039869 Error [security.kerberos] <0000040947/0x00000200> No key table entry found for red4$@SQLREPRO.EDU
11/22/2019 13:56:26.450053069 Debug [security.kerberos] <0000040947/0x00000200> SSPI operation 0x0000000F returned status: KerberosStream.cpp:2021 Operation unsuccessful
11/22/2019 13:56:26.450119868 Debug [security.kerberos.libos] <0000040961/0x0000020c> GetSecContextByUserABI() return value: 0x80090304
11/22/2019 13:56:26.488617991 Debug [security.kerberos.libos] <0000040961/0x0000020c> QueryContextAttributes() return value: 0x00000000
11/22/2019 13:56:26.488748289 Debug [security.kerberos.libos] <0000040961/0x0000020c> QueryContextAttributes() return value: 0x00000000
11/22/2019 13:56:26.489370580 Debug [security.kerberos.libos] <0000040961/0x0000020c> LookupAccountSid() return value: 0x00000001
Why?
When you run queries like ‘create login’, it will cause permissions to be checked. The first time you do it, it invalidates current permission. When you do it again, the permission check will be rechecked. When doing the permission check, SQL Server has to go through the mssql.keytab to pick up the machine entry key or MSA key. If SQL Server does not find the entries or find invalid entries, it raises error in question.
Here are three common scenarios that will cause the issue.
1.The machine account entries are not added. (mssql.keytab is configured by machine account)
If you skip the step ‘Option 1: Using UPN to configure the keytab’ in article https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql…, you will run into this issue.
For example, here is a screenshot of valid mssql.keytab. If you don’t have the entries highlighted, error 15404 happens.
2.Credentials of the MSA are not added. (mssql.keytab is configured by Managed Service Account)
This is similar to the scenario 1.
For example, here is a screenshot of valid mssql.keytab. If you don’t have the entries highlighted, error 15404 happens
3.The machine account entries are expired. (mssql.keytab is configured by machine account)
To my knowledge, updating the machine account password cause the KVNO increase, and the machine account entries stall.
If someone in the SQL Server Linux runs following command: adcli update –computer-password-lifetime=0. (0 is just an example which cause password update immediately )
It will update the machine account password and caused the KNVO increase.
Please note, Windows domain controller does not control the machine account password update. It’s the client decision to update the password or not.
Even you have the ‘Domain member: Maximum machine account password age’ policy enabled, domain controller does not force clients to update machine account password.
Solution
===
1.For scenario 1 &2, just add then entries back.
2.For scenario 3, you need to recreate the mssql.keytab.
The ‘adcli update’ commands update machine account password, and refresh the /etc/krb5.keytab. You need to following the step ‘Option 1: Using UPN to configure the keytab’ , but with some changes.
The original steps is :
Now, you need to ‘Delete all the entries by their slot number that are not the UPN’, and delete all stalled UNP entries. Only UPN with latest KVNO are left.
For example, here is a screenshot of /etc/krb5.keytab.
You need delete all the entries, keep the entries highlighted.
One of my client was running into the below error running xp_logininfo on some of their users. It was interesting that when they run the script to capture user information it works for the same user in another domain. They asked me if there is anything specific they can look for to find out the difference between the two users in the different domains?
Msg 15404, Level 16, State 4, Server BRSQL3, Procedure xp_logininfo, Line 43
Could not obtain information about Windows NT group/user ‘INDIA\SQLGroup’, error code 0x2147.
Msg 15404, Level 16, State 4, Server VISQL3, Procedure xp_logininfo, Line 43
Could not obtain information about Windows NT group/user ‘EMEA\SQLGroup’, error code 0x2147.
I converted hex code 0x2147 to decimal (using the calculator as below)
And it was error number 8519 which means “A global group cannot have a cross-domain member.” As per the message, it seems like something to do with the type of group they were trying to add.
The error message helped them and they were able to find what was mentioned in the error message.
ANOTHER VARIATION
Below is another flavor of the error which you might see.
Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user ‘ASIA\SQL_svc’, error code 0x5.
The above error is generally reported in SQL Agent job failure. Notice that error code is 0x5 which is in hexadecimal. This is equivalent to decimal 5 = Access is denied.
TIP AND TRICK
You need not remember the message text. If you now any message ID for windows error, you can convert it to text using net helpmsg command.
The above can be fixed by changing the owner in job properties to ‘SA’ and it should fix 0x5 error.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Related Posts
Have you ever encountered the error message “Msg 15404, Level 16, State 11, Line 6 Could not obtain information about Windows NT group/user ‘SQL2019TESTENVDopey’, error code 0x534” while working with SQL Server? If so, you’re not alone. This error can be quite puzzling, but fear not, we’re here to shed some light on it.
The reason behind this error is actually quite simple. It occurs when you try to access an Active Directory (AD)/Windows ID that no longer exists or is inaccessible, but the corresponding entry still exists within SQL Server. This often happens when someone leaves a company or when a service account is removed without removing the associated SQL Server logins and users.
So, how do you fix this error? Well, it depends on the situation. If the ID is supposed to be gone, you can simply drop the associated logins and users. However, if the ID still exists in AD/Windows but the Security Identifier (SID) no longer matches, the process becomes a bit more complicated.
To fix this scenario, you’ll need to script the login (server principal) along with its server-level role memberships and permissions. You can use tools like the sp_SrvPermissions stored procedure to help with this task. Once you have the script, you can drop and re-create the login, ensuring that the new SID matches the one in AD/Windows.
It’s important to note that you don’t need to drop any users (database principals) in this situation. Instead, you’ll need to execute the following command in each database where a related user exists:
ALTER USER Dopey WITH LOGIN = 'Dopey'
This command will update the SID of the user to match the login, resolving the mismatch between SQL Server and AD/Windows.
It’s worth mentioning that this error can also occur with Azure SQL databases and Azure Active Directory (AAD), although further testing may be required to confirm this.
Next time you encounter the SQL Server error Msg 15404, you’ll have a better understanding of what’s causing it and how to resolve it. Remember to always ensure that your SQL Server logins and users are in sync with the corresponding AD/Windows IDs to avoid encountering this error in the future.
Table of Contents
SQL Server Error: 15404
Severity: 16
Event Logged or not: No
Description:
Could not obtain information about Windows NT group/user ‘%ls’, error code %#lx.
Severity 16 Description:
Indicates general errors that can be corrected by the user.
Error Description:
The job failed. Unable to determine if the owner (Domain\VBR) of job L14-Server has server access (reason: Could not obtain information about Windows NT group/user ‘Domain\VBR’, error code 0x5. [SQLSTATE 42000] (Error 15404)).
“The job failed. Unable to determine if the owner (DOMAIN\administrator) of job Agent history clean up: distribution has server access (reason: Could not obtain information about Windows NT group/user ‘DOMAIN\administrator’, error code 0x5. [SQLSTATE 42000] (Error 15404)).”
Reading sql server error log location from SQL Query
Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go
For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html
Solution for Resolving the Error
When an invalid principal is supplied in authentication, the code 15404 is used. Impersonation of a Windows account also fails because the SQL Server service account and the Windows account’s domain do not have a full trust relationship.
Solution for Error SQL Server 15404
Verify that the Windows principal is there and that it is not misspelt.
If the error is caused by a lack of full trust between the SQL Server service account and the Windows account’s domain, one of the following actions can be taken to fix it:
For the SQL Server service, use a user account from the same domain as the Windows user.
If SQL Server uses a machine account such as Network Service /Local System, the domain holding Windows User must trust the machine.
In Simple words, Active Directory is refusing access to your SQL Agent. The Agent should be running under an account that is recognized by domain controller.
SQL Server Error Code 15404 and solution summary
SQL Server Error: 15404
Severity: 16
Event Logged or not: No
Description:
Could not obtain information about Windows NT group/user ‘%ls’, error code %#lx.