Ошибка 0x534 sql server

Have you ever changed Server name on which SQL Server instance is installed? One of my friends changed the hostname of a Windows server with SQL Server already installed. After this, the SQL Server maintenance plan jobs started to fail.  As we know, internally SQL Server still shows the old hostname this must be dropped manually. Otherwise your SQL Server maintenance plan jobs fail with this error.

The Job failed: Could not obtain information about Windows NT group/user 'XXXXXX\Administrator', error code 0x534. [SQLSTATE 42000] (Error 15404))

In this post, I will show you the procedure to resolve the errors and execute the SQL Server Agent Maintenance Plan jobs successfully. Below is the error screenshot showing job failure in the SQL Server agent logs. The error is highlighted in the image in red.

First, connect to your SQL Server instance with SQL Server Management Studio and run the below queries to check SQL Server name:

use master
select @@SERVERNAME -- The current hostname SQL Server recorded
select SERVERPROPERTY('machinename') -- The hostname the operating system recorded

In the below screenshot, the server name and machine name are different.

Run the below shown T-SQL scripts to drop the old server name, and then it add back the SERVERNAME to match the operating system’s hostname.

In the below screenshot, first we dropped old server name.

In the below screenshot, we have added new server name using T-SQL.

Now, log into the SQL Server with a “sysadmin” privileged user. Go to SQL Server logins, and you can still see the oldServername\administrator login bound with the SQL Server engine.

Drop the login “OldServername\administrator” and create a new windows login as “NewServername\administrator”, adding the sysadmin Server role.

CREATE LOGIN [NewServername\administrator] FROM WINDOWS;
GO
EXEC sp_addsrvrolemember N'NewServername\administrator', N'sysadmin';

In the below screenshot, we have added “DB01\administrator” login.

The owner of the job associated with maintenance plan is OldServername\administrator. We need to reset the ownerid using the below T-SQL Update query.

Now, We need to reset the owner of the job associated with the maintenance plan by running the below T-SQL query. In below screenshot, reset the owner of the job.

Right click on SQL Server job and select properties and change the owner of job to “sa” login.

Delete old maintenance plan and re-create the maintenance plan. Right click and click execute maintenance plan. You can see maintenance plan executed successfully. J

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Linkedin

Email: Gana20m@gmail.com

Источник
  • Remove From My Forums

  • Question

  • hello,

    we have changed the name of MS SQL server 2005 from XYZ to ABC using

    sp_dropserver <old_name>

    GO

    sp_addserver <new_name>, local

    GO

    Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error

    Date  03.05.2008 16:00:00
    Log  Job History (ADM_AdminDB_TP_Backup.Subplan_1)

    Step ID  0
    Server  ABC
    Job Name  ADM_AdminDB_TP_Backup.Subplan_1
    Step Name  (Job outcome)
    Duration  00:00:00
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    The job failed.  Unable to determine if the owner (XYZ\SQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user ‘XYZ\SQLServer’, error code 0x534. [SQLSTATE 42000] (Error 15404)).

    please help us in this  issue

Answers

  • hello,

    The problem is solved now…. we have drop the old maitenance plan recreated a new one.

    Regards,

    Pavi

Источник
  • Remove From My Forums

  • Вопрос

  • hello,

    we have changed the name of MS SQL server 2005 from XYZ to ABC using

    sp_dropserver <old_name>

    GO

    sp_addserver <new_name>, local

    GO

    Now our maitenance plan is getting failed we are not able to execute backup jobs we are getting following error

    Date  03.05.2008 16:00:00
    Log  Job History (ADM_AdminDB_TP_Backup.Subplan_1)

    Step ID  0
    Server  ABC
    Job Name  ADM_AdminDB_TP_Backup.Subplan_1
    Step Name  (Job outcome)
    Duration  00:00:00
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0

    Message
    The job failed.  Unable to determine if the owner (XYZ\SQLServer) of job ADM_AdminDB_TP_Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user ‘XYZ\SQLServer’, error code 0x534. [SQLSTATE 42000] (Error 15404)).

    please help us in this  issue

Ответы

  • hello,

    The problem is solved now…. we have drop the old maitenance plan recreated a new one.

    Regards,

    Pavi

Источник

А что делать есть в сообщении указан не текущий логин, а старое имя компьютера? Как сказать SQL-серверу, что у меня новое имя компьютера? Вроде бы воспользовался командой

T-SQL 
1

ALTER AUTHORIZATION ON DATABASE::[mydbname] TO [DOMAIN\user]

, где [mydbname] – имя базы данных, DOMAIN и user – имя ПК и пользователя соответственно, а ошибка всё равно сохранилась, а владелец не изменился (при том, что SQL запрос прошёл успешно)

Добавлено через 23 минуты
Удалось получить доступ, переименовав имя для входа в «безопасность, имена для входа». Не думал, что это как-то исправит ошибку

Источник

May 6, 2020 by Kenneth Fisher

This is an interesting error that you’ll occasionally get when accessing an AD/Windows ID.

Msg 15404, Level 16, State 11, Line 6
Could not obtain information about Windows NT group/user ‘SQL2019TESTENV\Dopey’, error code 0x534.

Pretty simple reason here. The AD/Windows group/user no longer exists (or is inaccessible) but the entry for it exists inside of SQL.

Now what do I mean by “accessing”? Well the easiest way to get the error is to try to impersonate the id.

EXECUTE AS LOGIN = 'SQL2019TESTENV\Dopey'; 
EXECUTE AS USER = 'SQL2019TESTENV\Dopey';

I’ve most frequently seen this happen when someone has left the company (or a service account is removed for whatever reason) and the corresponding SQL principals (logins & users) have not been removed.

When this can get really interesting when you go to look up the name in AD/Windows and it’s still there!?! Basically what’s happened is that the SID has changed at the AD/Windows level. I believe this can happen when removing/re-adding an id but when I tested in Windows dropping and re-creating the Id gave me the same SID. I could be missing something though. Regardless the SID in SQL no longer has a match in AD/Windows.

Now that we have the error what do we use to fix it? Well, if the Id is gone and is supposed to be gone drop your associated logins and users. If on the other hand the Id still exists but the SID doesn’t match anymore it’s a bit more complicated.

First script the login (server principal) and all of it’s server level role memberships and permissions then drop and re-create it. Generally I use my sp_SrvPermissions stored procedure for this. You do not have drop any users (database principals). You will have to do the following in each of the databases were a related user exists.

ALTER USER Dopey WITH LOGIN = 'Dopey'

This will change the SID of the user to match the login.

To the best of my knowledge this can also happen with Azure SQL databases and AAD although I haven’t tested it yet.



Category: Microsoft SQL Server, Security, SQLServerPedia Syndication

| Tags: Microsoft SQL Server, security

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 0x8004230f непредвиденная ошибка поставщика теневого копирования
  • Ошибка 0x51 ssd
  • Ошибка 0x500a0190 red dead online
  • Ошибка 0x4d epson
  • Ошибка 0x50000006 red dead online