Restore read-only filegroup

If you are missing a file in a read-only filegroup, you can offline that file (which will of course offline the whole filegroup), and continue working with other filegroups. Any queries over global indexes will work fine.

• ALTER DATABASE dbdb MODIFY FILE (NAME = 'filename_april2', OFFLINE)

If, at this point, you managed to restore the corrupted file (let's call it filename_april2), you can recover the whole filegroup and continue working without any impact on the rest of the database. Neat.

• RESTORE DATABASE dbdb FILEGROUP='april_fg' WITH RECOVERY

Note: Each filegroup has a primary and secondary files. This restore applies to either primary or secondary file, as long as the filegroup is read-only.

Login failed for user 'Admin' with SQL 18456 State 5

A client receives error when running MS Access application and querying tables linked via ODBC. The error is:

Connection failed:
SQLState: '01000'
SQL Server Error: 53
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied 

What shows up in the SQL log is:

Login failed for user 'Admin'. [CLIENT: xx.xx.xx.xxx]
Error: 18456, Severity: 14, State: 5.

Possible solution:

By default, Access Jet engine tries to use its own username to connect to SQL Server BEFORE the predefined SQL connection. "Admin" is usually defined as the default Access username. There is a registry entry that switches that default behavior off:

Registry value: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ODBC\TryJetAuth
Change from 1 to 0.

Auditing in SQL Server 2005 - DDL triggers

If you need to implement server/DDL auditing in SQL 2005, you have to either use notifications or DDL triggers. This note is just about DDL triggers.

On a database level, you can audit all database events with a DDL trigger:

CREATE TRIGGER AuditTrigger
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS  AS..

On a server level, if you attempt to create trigger for all server events:

CREATE TRIGGER AuditTrigger_Server

ON ALL SERVER  FOR DDL_SERVER_LEVEL_EVENTS AS...



you will get the following error:

"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration

That's for 2 reasons: DDL_DATABASE_EVENTS cannot be used in DDL trigger on server level and there is a bug if you use DDL_SERVER_SECURITY_EVENTS.

Workaround for both issues:

CREATE TRIGGER AuditTrigger_Server

ON ALL SERVER FOR DROP_DATABASE, CREATE_DATABASE, ALTER_DATABASE, DDL_login_events, DDL_gdr_server_events, DDL_authorization_server_events  AS...

Future versions:
SQL 2008 and 2012 have a new, easy way to implement auditing functionality using
CREATE SERVER/DATABASE AUDIT [SPECIFICATION]... commands

To change the default backup folder on SQL cluster

• Use SQL Mgmt Studio 2008.
• Right click on server/facets
• Change BackupDirectory to the new value
  Click OK

Restore Service Master Key

As a part of a DR test today, I recovered the encrypted database (log shipped) and restored the service master key from the production. When restoring the key, the following error showed up:

Msg 15320, Level 16, State 12, Line 1
An error occurred while decrypting master key 'DbName' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

Fix
Use the FORCE option. Although the documentation says that you might lose some data, as long as you have the correct service master key from the production, that will not happen. Quite the contrary, the correct master key will decrypt the database.

Tags: SQL Server error 15320, service master key, restore

SQL Cluster: Resource dependencies

When installing a new clustered instance, you will probably want to assign multiple disk drives to a SQL instance. After you define all clustered resources and install instance, instance will only see the disk drive where you put your default data files. For any additional disk drives, you have to manually apply the solution below.

Solution:
In a SQL Cluster Administration, open the "SQL Server" resource, click on "Dependencies" and add the disk resource you want to be able to use.

Key words:
"SQL cluster" "Select the folder:" "can't see disk"

Linked server for 64-bit SQL 2005 SP3

I was supposed to set up the linked server from SQL Server to Oracle 10g database. Initially, I tried to use the Microsoft provider. After registering the 32-bit MDAC for Oracle and setting up the linked server, I got an error:

• C:\Program Files (x86)\Common Files\System\Ole DB>regsvr32 msdaora.dll
• EXEC sp_addlinkedserver 'CFS', 'Oracle', 'MSDAORA', 'oracle10g'
• EXEC sp_addlinkedsrvlogin 'CFS', 'FALSE',NULL, 'scott', 'tiger'
• SELECT * FROM CFS..SCOTT.SOME_VIEW
• Msg 7308, Level 16, State 1, Line 1
• OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

This was the 32-bit provider and apparently this is a known bug.

I switched to Oracle OLEDB Provider and it worked without issues:

• USE [master]
• GO
• EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
• GO
• EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1
• GO
• EXEC master.dbo.sp_addlinkedserver @server = N'CFS', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'CFS'
• EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CFS',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='tiger'

The other solution would be to install 64-bit MDAC on the Windows server. MDAC has been complied to both 32-bit and 64-bit files since the version 2.7, but it is limited to only few platforms and we couldn't find 64-bit MDAC for Windows 2003.

The only drawback with using the Oracle provider is that for any errors, we have to go to Oracle to get a support.