November 6, 2008 at 4:41 am
Hi all,
I have a weird problem when I'm creating linked server using trusted connection. The problem is as follow:
- If I'm querying server B from server A (SELECT * from B.Database.Schema.Table) and the object exists on both servers it gives a result from the server A (using profiler I can't see any activity on server B)
- If the object doesn't exist on server B the error is:
"Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "B" does not contain the table ""Database"."dbo"."Table"". The table either does not exist or the current user does not have permissions on that table" (I'm a sysadmin on both servers and using profiler I can't see any activity on server B).
Please, I need an answer ASAP.
P.S. Both SQL servers are SQL Server 2005 x64 Enterprise edition with SP2 and hotfix 3152 (KB933097)
Windows Servers are: Microsoft Windows Server 2003 R2 Enterprise Edition, SP2. With update KB932755
In Active Directory "Account is sensitive and can not be delegated" for my account is not checked.
On server A:
SETSPN -A MSSQLSrv/serverA:1433 Domain\ServerA_ServiceAccount
SETSPN -A MSSQLSrv/ServerA.Domain.com:1433 Domain\ ServerA_ServiceAccount
For the ServerA_ServiceAccount and computer account "Trust this user for delegation to specified services only" (Use Kerberos only) is checked.
On Server B:
SETSPN -A MSSQLSrv/serverB:1433 Domain\ServerB_ServiceAccount
SETSPN -A MSSQLSrv/ServerB.Domain.com:1433 Domain\ ServerB_ServiceAccount
For the ServerB_ServiceAccount and computer account "Trust this user for delegation to specified services only" (Use Kerberos only) is checked
November 6, 2008 at 5:00 am
just checking the basics....are you using a full path in the query, for example
select * from ServerB.DbName.dbo.Employees where Ename='Bob'
could you please show a sample query, where a table exists in both servers, but it comes from server A instead of B?
Lowell
November 6, 2008 at 5:16 am
Table exists on both server:
SELECT * FROM ServerB.MyDatabase.dbo.TableSize
Table exists only on Server B
SELECT * FROM ServerB.MyDatabase.dbo.BackupHistory
November 6, 2008 at 5:38 am
Hi Dejan
Try to Query from and other database on the source server and see of this works.
Cheers
🙂
November 6, 2008 at 6:16 am
Sorry but I've tried from master and from other databases but didn't succeed.
November 6, 2008 at 8:02 am
Can somebody please provide me a link where I can find step by step how to setup account delegation (Kerberos), because I think that somehow, this is a problem... At least, I will be sure that the setup is ok.
Thanks,
November 6, 2008 at 8:48 am
Try using the sa or equilavent instead of the Network ....i never got Network/Acct to work.
November 6, 2008 at 8:51 am
Hi Dejan,
Below you vill find links to two articles but I was just wondering; if you SELECT * from sys.servers - is there a correct entry for your server B?
"Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "B" does not contain the table ""Database"."dbo"."Table"". The table either does not exist or the current user does not have permissions on that table" (I'm a sysadmin on both servers and using profiler I can't see any activity on server B).
Did you try with passing on a SQL Server account instead just to confirm that there is a problem with delegation?
http://support.microsoft.com/kb/319723, "How to use Kerberos authentication in SQL Server"
http://support.microsoft.com/kb/811889,
How to troubleshoot the "Cannot generate SSPI context" error message
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 2:00 am
Hello again,
I'm one step up 🙂 with my linked server. Now, when I create a linked server in profiler I see that the user who is accessing is AnonimusLogin, instead of the windows user who is querying the server B.
Any ideas what is going on?
November 7, 2008 at 2:42 am
Hi,
Does that account in question have the Access this computer from Network user right?
/elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 2:56 am
Hi,
Can you run scripts? In that case you could script you objects but you probably want to apply constraints after the data export. Also, if you bring over your IDENTITY columns, SET IDENTITY INSERT ON has to be used.
Best option is if you have SSIS, then there is a Transfer SQL Server objects task that can do the job for you.
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 3:24 am
Yes, it has
November 7, 2008 at 3:30 am
Yes, it has
November 7, 2008 at 4:56 am
Hi,
I can't setup the scenario to test myself at the moment but I checked the sql_protocols blog at MSDN and found the following blog:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
(it starts off with a Login failed message but I assume that is would you would get if the Anonymous login were not allowed access).
I do hope it helps and if you resolve the issue, please let us know how!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 5:52 am
how about some screen shots and did you try a sql login to see if that at least works.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply