Configuring SQL server linked server between standalone instance and mirrored instance to is easy. Isn’t it?

While configuring linked server from Mirrored instance to standalone instance, there was an error of :

The OLEDB provider “SQLNCI” for linked server “XXX.XX.X.XUAT” reported an error. Authentication failed. Cannot initialize the data source object of OLEDB provider “SQLNCI” for linked server “XXX.XX.X.XUAT”. OLEDB provider  returned message “ Invalid authorization specification” Microsoft SQL Server, Error 7399

Here is what to do , when we get this kind of error:

First of all, we need to check if we have permissions on DTC :

1. Open the DCOM Configuration utility
Method: Start -> Run -> type dcomcnfg

2. Expand Component Services/Computers/My Computer Right click on My Computer and select Properties.

3. On the Default Properties tab, check the following:
a. Make sure that “Enable Distributed COM on this computer” is checked
b. Make sure that Default Authentication Level = Connect
c. Make sure that Default Impersonation Level = Identify or Impersonate

4. On the COM Security tab, check the following:
a. In the Access Permissions section, click the Edit Limits and Edit Default buttons
b. Under each button, make sure that the local group containing the SQL Service account  or the SQL Service account itself has Allow on all permissions
c. In the Launch and Activation Permissions section, click the Edit Limits and Edit Default buttons
d. Under each button, make sure that the local group containing the SQL Service  account or the SQL Service account itself has Allow on all permissions.

5. On the Default Protocols tab, check the following:
a. Check to verify that Connection-oriented TCP/IP exists in the DCOM Protocols

6. After implementing the changes reboot the server for the changes to take affect

Also, when we are configuring linked server are we giving the failover partner name as well in @provstr (http://msdn.microsoft.com/en-us/library/ms190479.aspx ). It will make sure that linked server is usable even if the failover happens. Here is the command to do that:

EXEC master.dbo.sp_addlinkedserver
@server = N’GATEST10′,
@srvproduct=N”,
@provider=N’SQLNCLI’,
@catalog= Mirror,
@provstr=N’Server=GAURAV-2K8SQL2012;Failover Partner=GAURAV-2K8SQL12;’

HTH!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s