The server principal owns an endpoint and cannot be dropped

Find the user holding the endpoint connection by running:

SELECT p.name, e.* FROM sys.endpoints e
inner join sys.server_principals p on e.principal_id = p.principal_id

Then delete the endpoint which is causing the issue (if it is not in use!)

This should then allow you to drop the login.

Add a schema to the External Network Services Access Control List

From Oracle 11g network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR which can be used to access external network resources, are more restricted and secured. Oracle 11g introduced Fine-Grained Access to these packages by creating an Access Control List to use any external network resource through these packages. Before this any user who had an execute privilege on these packages was able to do anything to any network resource like web and local mail servers etc. But now a user needs a little more then just an execute privilege on the network packages.

Run the following as SYS to add a schema into the access control list

BEGIN

 dbms_network_acl_admin.add_privilege ( 
   acl         => 'netacl01.xml', 
   principal   => 'MYSCHEMA',
   is_grant    => TRUE, 
   privilege   => 'connect'
 );

 dbms_network_acl_admin.add_privilege ( 
   acl         => 'netacl01.xml', 
   principal   => 'MYSCHEMA',
   is_grant    => TRUE, 
   privilege   => 'resolve'
 );

 COMMIT;

END;

Or by doing this:

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl01.xml' ,'MYSCHEMA', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl01.xml' ,'MYSCHEMA', TRUE, 'resolve');
commit;

To test, send a mail as that user:

alter session set current_schema=MYSCHEMA;
exec send_mail.mail('mos@domain.co.uk','mos@domain.co.uk','Testing ACL - ### SENDMAIL ###','Test');

Note: This requires the sendmail package to be setup in the schema

Drop a Schema from the ACL:

exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('netacl01.xml' ,'MYSCHEMA');
exec DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('netacl01.xml' ,'MYSCHEMA');
commit;

To see permissions who has permissions for ACL:

SELECT acl , principal , privilege , is_grant 
FROM DBA_NETWORK_ACL_PRIVILEGES;

Troubleshooting

ERROR at line 1:

ORA-44416: Invalid ACL: Unresolved principal ‘MIKE_R’

ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 421

ORA-06512: at line 1

This indicates that a user exists in the ACL which has been dropped from the database. Follow the Drop command above to resolve this.

ERROR at line 1:

ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_TCP", line 17

ORA-06512: at "SYS.UTL_TCP", line 246

ORA-06512: at "SYS.UTL_SMTP", line 115

ORA-06512: at "SYS.UTL_SMTP", line 138

The above error indicates that the schema has not been added into the ACL

Oracle data block corruption errors have been found in the alert log

First time I’ve seen this:

Wed Mar 16 00:30:03 2011
Corrupt Block Found
         TSN = 36, TSNAME = TS_UK_MYSCHEMA_DATA
         RFN = 33, BLK = 1939619, RDBA = 140351651
         OBJN = 631282, OBJD = 631282, OBJECT = I_MYSCHEMA_2, SUBOBJECT =
         SEGMENT OWNER = MYSCHEMA, SEGMENT TYPE = Index Segment
Errors in file /u01/app/oracle/diag/rdbms/db1/db12/trace/db12_j003_5565.trc  (incident=335735):
ORA-01578: ORACLE data block corrupted (file # 33, block # 1939619)
ORA-01110: data file 33: ‘/oradata/DB1/DB1/ts_myschema_data01.dbf’

I resolved by dropping the Index which was in the corrupt block, and then recreating the index. The refresh job is working ok now.

The next step would be to run a DB Verify against all databases to make sure there isn’t any other corruption and hasn’t been hit by a query yet.

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’–SPN’s\Kerberos

Message
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]
Message
Error: 18456, Severity: 14, State: 11.

As far as I understand the above error happens because the CLNTSQL2 is currently running off its secondary (SQL01a) and is failing because it cant use Kerberos authentication so it is falling back onto NTLM because the Service Principal Name for sql server isn’t registered properly.

Understanding Kerberos and NTLM Authentication

As can be seen below, when the instance started up, it failed to register the Service Principal Name (SPN). This isn’t something new – in that I don’t think SPN’s have ever been set up for the SQL Servers – BUT it looks to write the login failed error to the alert log when the cluster instance isn’t on its registered node:

Date          16/03/2011 11:03:45
Source        Server
Message
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.

See Step 3 http://technet.microsoft.com/en-us/library/ms189585(SQL.90).aspx

To manually create a domain user Service Principle Name (SPN) for the SQL Server service account

1. Click Start, click Run, and then enter cmd in the Run dialog box.

2. From the command line, navigate to Windows Server support tools installation directory. By default, these tools are located in the C:\Program Files\Support Tools directory.

3. Enter a valid command to create the SPN. To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.4. Verify that the command completed successfully by reviewing the command’s output for the updated object line.

Important: When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.

To create an SPN for the NetBIOS name of the SQL Server use the following command:

setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>

To create an SPN for the FQDN of the SQL Server use the following command:

setspn -A MSSQLSvc/<SQL Server FQDN>:1443 <Domain\Account>

Note: The command to register an SPN for a SQL Server named instance is the same as that used when registering an SPN for a default instance except that the port number should match the port used by the named instance.

Based on this, I would assume registering vmSQL02 on SQL01a would be as follows:

setspn –A MSSQLSvc/vmSQL01.Domain.x.co.uk:1748 GROUP\SQLAPP.Service

setspn –A MSSQLSvc/vmSQL02.Domain.x.co.uk:2487 GROUP\SQLAPP.Service

And on SQL01b

setspn –A MSSQLSvc/vmSQL01.Domain.x.co.uk:1748 GROUP\SQLAPP.Service

setspn –A MSSQLSvc/vmSQL02.Domain.x.co.uk:2487 GROUP\SQLAPP.Service

SETSPN can be downloaded from here

C:\Program Files (x86)\Resource Kit>setspn -L SQL01a

Registered ServicePrincipalNames for CN=SQL01a,OU=SQL,OU=Servers,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

    HOST/SQL01a
    HOST/SQL01a.domain.x.co.uk

C:\Program Files (x86)\Resource Kit>setspn -L SQL01b
Registered ServicePrincipalNames for CN=SQL01b,OU=SQL,OU=Servers,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

    HOST/SQL01b
    HOST/SQL01b.Domain.x.co.uk

C:\Program Files (x86)\Resource Kit>setspn -L GROUP\SQL.Service
Registered ServicePrincipalNames for CN=APP SQL Service,OU=Service Accounts,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

C:\Program Files (x86)\Resource Kit>setspn -L SQL01
Cannot find account SQL01

C:\Program Files (x86)\Resource Kit>setspn -L SQL02
Cannot find account SQL02

C:\Program Files (x86)\Resource Kit>setspn -L MSSQLSvc/SQL02
Cannot find account MSSQLSvc/SQL02

Prevent Backup info writing to Errorlog

Whenever a backup is taken by default SQL Server will write the detail to the errorlog as shown below.

image

The information is available in the backupset table on MSDB database. We can achieve this by using the trace flag 3226. Follow the steps below to add this trace flag to sql server startup.

  • Open SQL Server Configuration Manager
  • Go to SQL Server properties and click on Advanced Tab
  • Add Trace flag 3226 as shown below
    suppress_backup_2 

  • Restart SQL Server service

Dropping Database – Delete Backup History taking a long time

Ive found this on numerous occasions and decided not to be lazy this time around and investigate why it takes so long to drop a database when the ‘Delete backup and Restore History Information for databases’ checkbox is ticked.

When thinking about it logically, when purging historical data with a where criteria against a set of tables in any database, the task would be faster when you have indexes in place. this is exactly the scenario which causes the GUI to seem like its hanging. However, if you check sys.dm_exec_requests you will be able to see CPU and IO actively moving and a DELETE command against the msdb, in the form of:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDB'

Running the following will show you which indexes are present on the msdb database as standard:

SELECT t.name as [Table], ind.name as [Index], col.name as [Column], ind.type_desc [Index Type]
FROM sys.indexes ind
inner join sys.index_columns ic
    on ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join sys.columns col
    on ic.object_id = col.object_id and ic.column_id = col.column_id
inner join sys.tables t
    on ind.object_id = t. object_id WHERE ind.is_unique = 0 and ind. is_unique_constraint = 0
    and t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 'restorehistory' ) ORDER BY t .name, ind.name

…Only 4 indexes are present as standard in the msdb database.

This problem can be alleviated by adding a set of indexes to some key tables in the msdb database.

This script is taken from Here

/************************************************************************ * * * Title: msdb index creation * * Author: Geoff N. Hiten * * Purpose: Index msdb database * * Date: 12/12/2005 * * Modifications: * ************************************************************************/ use msdb
go

--backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go
Create index IX_backupset_media_set_id on backupset(media_set_id) go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go
Create index IX_backupset_backup_start_date on backupset(backup_start_date) go

--backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go

--backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go

--backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go

--restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go

--restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go

--restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go

This small addition will speed up the deletion process completely and make sure the GUI doesnt hang waiting for a background delete against the msdb to complete.

Useful scripts

I found that deleting backup history was slowing down, so i did the following:

USE msdb; GO
UPDATE STATISTICS backupfile; GO
UPDATE STATISTICS backupmediafamily; GO
UPDATE STATISTICS backupmediaset; GO
UPDATE STATISTICS backupset; GO
UPDATE STATISTICS restorefile; GO
UPDATE STATISTICS restorefilegroup; GO
UPDATE STATISTICS restorehistory; GO

And also added the following index, after looking at the estimated execution plan:

Create index IX_backupset_database_name on backupset(database_name) go

Some general queries:

--Find count of records older than certain date select count(*) from backupset with (nolock) where backup_start_date < '8/15/2009' --with database name select * from backupset with (nolock) where backup_start_date < '08/15/2011' and database_name='MyDB' --Find databases which have history data but no longer attached to instance select distinct(database_name) from backupset with (nolock) where database_name not in (select name from sys.databases) --Purge history older than certain date sp_delete_backuphistory @oldest_date='08/15/2009' --Purge history for a particular DB EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myTest'