Ошибка sqllib системная таблица sysaltfiles в экземпляре sql server

• We are recieving this error in the application log of our sql server server.

  What does it mean?

  Nothing when I google it…

  Thanks,

  Thomas

  Thomas LeBlanc, MVP Data Platform Consultant

• rubes

  SSCrazy

  Points: 2058

  Interesting.  I just ran across this issue today.

  We had a 3rd party backup application installed.  It was running under an account that had select permissions on sys.databases but not sys.sysaltfiles.  It was throwing the exact error that you are seeing.  When we increased the permissions it went away.

  Regards,
  Rubes

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  It should be a permission issue as told by rubes give necessary permissions and check if you are able to

• Thomas LeBlanc

  SSCrazy Eights

  Points: 8074

  Check with Backup software and it is not using a agent to backup SQL Server. This is a 2005 server, and the only agent available in the software is for 2000.

  We use SQL Server to backup the data and transaction logs, then this 3rd party software only backups the backup files to tape.

  Thanks,

  Thomas

  Thomas LeBlanc, MVP Data Platform Consultant

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  I was looking for info on this aswell and I came across the following solution that I thought I’d share made me laugh a little:

  «resolved by granting the NT Authority System account the SysAdmin role.»

• grc1uk

  SSC Eights!

  Points: 849

  September 25, 2008 at 10:20 am

  #877150

  Hello

  How i can check what permissions are on the sys.sysaltfiles files?

  many thanks

  Gareth

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  September 25, 2008 at 7:15 pm

  #877400

  find the sys.sysaltfiles then

  :right click > properties > permissions

  chances are there will be nothing. I can’t remember what roles other than sysadmin have access by default.

  this what u were after?

  cheers

  Carlton..

• grc1uk

  SSC Eights!

  Points: 849

  September 26, 2008 at 3:23 am

  #877508

  you say to find the sys.sys files. How do i find those files??

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  September 28, 2008 at 2:38 pm

  #878204

  hi,

  sorry.

  If you using sql 2005?

  If so go to the database > Views > System Views > and fild sys.sysaltfiles

  If your using sql 2000 its a system table not a view

  Master database > system tables > sysaltfiles

  HTH

  Cheers,

  Carlton..

• grc1uk

  SSC Eights!

  Points: 849

  September 29, 2008 at 2:58 am

  #878291

  Hello

  Thanks for this, i have now found these files. I have no permissions set on these. What would be the recommended ones to set?

  Thanks in advance

  Gareth

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  September 29, 2008 at 2:08 pm

  #878602

  when you say ‘files’ you mean the DB files themselves? (DBname.mdf, DBname.ldf).

  My pref is to set security @ the MSSQL directory (for 2005 this is: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL) and take the inheritance setting off.

  Then assign full control to the sql run time account (the user that SQL server runs under). This way only SQL server can access that directory (all db files, log files, backups rah diddy rah).

  You will porobably find Domain admins etc in there too.

  Carlton..

• grc1uk

  SSC Eights!

  Points: 849

  September 30, 2008 at 3:36 am

  #878732

  Sorry i was refering to the sys.sysaltfiles when looking in the database at these then right clicking and selecting properties nothing is displayed in the Permissions

  I was wondering if anything should be there?

  Thanks

  Gareth

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  September 30, 2008 at 3:26 pm

  #879205

  Hi, you could but you wouldn’t.

  search books online for:

  «Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views»

  I’m guessing sys.altfiles is now only accessible by sysadmins as its 2005 equivalent is the sys.Master_files view.

  Cheers,

  Carlton..

• grc1uk

  SSC Eights!

  Points: 849

  OK, thanks for the info. Any ideas where else i should be looking for receiving the following error during a backup window. We are using Veritas / Symantec Backup Exec. We are getting

  sqllib error: sysdatabases in sql server instance is empty

  Any thoughts?

• Carlton Leach

  SSCarpal Tunnel

  Points: 4202

  see if you can figure out what account the backup software is running under (could be ‘NT Authoritysystem’ which is commoly disabled or removed for security reasons).

  My guess would be the backup software is using an login that has limited or no access to sql server.

  try running a Profiler trace and kicking off a backup, this may give you some idea as to what its using.

  cheers,

  Carlton..

Коллеги, здравствуйте.

Озадачились установкой точки отчетов на нашем SCCM2012 r2.

SQL сервер отдельный. SQL 2008R2. Имя SQLSRV

Точка отчетов устанавливается на нем.

На сервере с SCCM 2012R2 (SCCMSRV) запускаю установку точки отчетов на сервере SQLSRV. Проходим все этапы.

Ошибок не выдал. Но в журнале SMS_SRS_REPORTING_POINT последним в списке отображается сообщение:

«Диспетчер компонентов сайта обнаружил, что в этой системе сайта должен быть установлен данный компонент. Попытка установки компонента будет выполняться кажд.60 мин.до успешного завершения установки»

А сообщения об успешной установке так и не появилось. 

Установка производилась от уз, rоторая имеет полные права в SCCM, а так же права локального админа на сервере SQLSRV.

Что может быть не так? Или надо ждать достаточно долго? На текущий момент прошло уже 40 мин.

My Windows 2016 Essentials Server is throwing the a CAPI2 and SQLWRITER, errors each night.  It looks to be a permissions issue but I can’t figure out how to resolve it.

THE CAPI2 error is:

—

The SQLWRITER error is:

Sqllib error: System table sys.sysdatabases in SQL Server instance SERVERDBNAME is empty.

—

I found some threads on forum detailing steps to take using accesscheck & sc sdshow.  However, it didn’t look like my entries looked like the forum so I was hesitant to make any changes to these records.  Any help on fixing this issue is appreciated.

—

Thanks!

While trying to backup a SQL Server database using SQL Writer (snapshot backups using VSS framework), then you might notice the following error in the application event logs.

Log Name: Application
Source: SQLWRITER
Event ID: 24581
Task Category: None
Level: Error
Description:
Sqllib error: System table sys.sysdatabases in SQL Server instance <SQL Server Name> is empty.

The above error is a bit misleading because it doesn’t literally mean that all your databases on the SQL Server instance reported have disappeared. If you scour the application event logs, you will find that another error is reported along with this error during the same timeframe.

Log Name: Application
Source: SQLWRITER
Event ID: 24583
Level: Error
Description:
Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 28000, Native Error: 18456
Error state: 1, Severity: 14
Source: Microsoft SQL Server Native Client 10.0
Error message: Login failed for user ‘NT AUTHORITYSYSTEM’.
DBPROP_INIT_DATASOURCE: <SQL Server Name>
DBPROP_INIT_CATALOG: master
DBPROP_AUTH_INTEGRATED: SSPI

Now it is clear that the SYSTEM account is unable to log into the SQL Server instance mentioned in the error message above. If you look into the SQL Errorlog, you will find the following error message:

Error: 18456, Severity: 14, State: 11.
Login failed for user ‘NT AUTHORITYSYSTEM’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

State 11 is basically telling you that the account doesn’t have access to the SQL Server instance. The reason for the login failure is available on SQL Server 2008 instances and above. There is a catch here the Login Failed error message is only reported in the SQL Errorlogs and Application Eventlogs only if the System account is granted access to the SQL Server instance but not granted the right permissions to get the database list from the sys.databases catalog. The hex code 0x80040e4d corresponds to an authentication failure.

So why does this happen? When you initiate a backup of a SQL Server database through SQLWriter (VSS Framework), SQLWriter will try to connect to all online instances to build a list of files associated with each database. SQLWriter/VSS needs this information to create the exclusion list. The account that the SQLWriter service uses is the NT AUTHORITYSYSTEM account. So if you have multiple instances of SQL Server online on the same server, then the SYSTEM account needs to have SYSADMIN permissions OR the necessary permissions to run a query against the sys.databases catalog on the instance. This is a by-design requirement and documented in the KB Article mentioned below. However, if the SQL Server instance is not started, then you SQLWriter is not bothered about the instance(s) as I/O need not be frozen for database files for an instance which is shutdown.

919023    SQL Server 2005 connectivity and Volume Shadow Copy Service (VSS)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;919023

Excerpt from above article:

Additionally, because of the types of operations that the writer must perform, we recommend that you do not remove the NT AUTHORITYSYSTEM login from the sysadmin server role.

Now if you do not want to grant sysadmin permission to the System account, then you need to do the following to prevent the above error:

1. Grant db_datareader role to the SYSTEM account on the master database. This is required for other queries that the SQLWriter may need to executed during the course of the backup.
2. Grant ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database to the SYSTEM account. These are the minimum permissions required to query sys.databases table.

Normally the SYSTEM account is part of Built-in Administrators group on the box if it is not removed due to security hardening. You would need to grant the above permissions to the System account on all SQL Server instances that are installed and online on the server where you are trying to perform the VSS backup.

Another symptom of the problem manifests itself when you execute the command: vssadmin list writers. You will find that the above error message(s) are reported and the SQLWriter is not listed in the list of available writers.

One you have granted the SYSTEM account the necessary permissions, a quick way to verify that everything is working as expected is to run the vssadmin list writers command again. You might get the following output:

Writer name: ‘SqlServerWriter’
Writer Id: <GUID>
Writer Instance Id: <GUID>
State: [1] Stable
Last error: Non-retryable error

If you find that a non-retryable error is reported, then you need to restart the SQL Server VSS Writer service. Once this is done, run the command again and if there are no problems encountered, then you will get the following output for the SQLWriter.

Writer name: ‘SqlServerWriter’
Writer Id: <GUID>
Writer Instance Id: <GUID>
State: [1] Stable
Last error: No error

NOTE: If a SQL Server VSS (Snapshot) Backup is in progress for any instance on the server, then a restart of the SQL Server VSS Writer service should be deferred till the backup is completed or fails.