Thursday, February 23, 2012

Backup Encryption Key in SQL Server 2008/R2

When trying to backup Encryption Key from ‘Report Services Configuration Manager’ I received below error:

“Microsoft.ReportingServices.WmiProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070A90”

When verified came to know that the password I am providing for the backup file is not meeting the password complexity policy and hence its throwing error.

So I have tried password having capital letters, small letters and number combination password and it worked perfectly.

Sometimes error will be bit easier to study like if you check the below error I received in another situation it clearly has message stating that issue is with password complexity

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070A90
 ---> System.Runtime.InteropServices.COMException (0x80070A90): Password doesn't meet the complexity policy (Exception from HRESULT: 0x80070A90)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.BackupEncryptionKey(Byte[]& encryptedBytes, String password)
   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.BackupEncryptionKey(Byte[]& encryptedBytes, String password)

Thanks  !!!

Wednesday, February 1, 2012

Could not connect to server '' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .

Today, actually from 2 days I am trying to fix an issue with linked server. My aim is to trigger job of Server Test02 from server Test01. When I verified linked server it already exists between 2 servers. But when I tried below query from TEST01 server to trigger job of TEST02 server

EXEC test02.MSDB.dbo.sp_start_job @job_name = 'Remote Job'

it has thrown below error:

Server '' is not configured for DATA ACCESS.(Microsoft SQL Server, Error: 7411)

The error says Linked server configured is not having DATA ACCESS. So to provide data access I ran below command:

EXEC master.dbo.sp_serveroption @server=N'TEST02', @optname=N'data access', @optvalue=N'TRUE'

After running above command it fixed the data access issue but I received another error:

Could not connect to server 'TEST02' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

This error was tricky it was not giving any user name for which remote login is not defined. Then verified the options of linked server and ‘Be made using the login’s current security context’ is checked under ‘security’ tab of linked server properties. ‘RPC Out’ option under ‘Server Options’ was also set to ‘TRUE’. With these settings linked server should work. When tested the linked server connection manually by right clicking on linked server the test is successful.

Then ran ‘select * from sys.sysservers’ and output of this has given me the issue details. The ‘isremote’ value should be ‘0’ for linked server to trigger jobs or SP of linked server but as expected the value of ‘isremote’ was ‘1’. Hence came to confirmation here the issue lies.

I am using SQL SERVER 2008 R2 and to change the value from ‘1’ to ‘0’ instead of updating system tables directly I dropped and created the linked server and YES this fixed the issue.

So for the error below:

Could not connect to server 'servername' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

Dropping and re-creating the linked server resolved the problem.