Does server linking work without Security Account Delegation?

Hi,
In short, the question is as follows:
Is there any way to make server linking working without Security Account
Delegation enabled if the target (linked) server is set to "Windows only"
authentication?
We've encountered the following problem:
There are two SQL 2000 servers: SERVER1 and SERVER2.
We want to be able to access data stored on SERVER2 from SERVER1.
Therefore we created on SERVER1 a linked server SERVER2 (sp_addlinkedserver
N'SERVER2', N'SQL Server').
If SERVER2 were set to "SQL Server and Windows" authentication we could
easily add a linked server login:
sp_addlinkedsrvlogin @.rmtsrvname = N'SERVER2', @.useself = 'FALSE',
@.locallogin = <some local login>,
@.rmtuser = <SERVER2_SQLLogin>, @.rmtpassword = <password>
However, SERVER2 is set to "Windows only" authentication and we cannot
change it.
How can we establish a link?
Many thanks for any help.
Best regards,
Andrewif the NT logins have access to both servers then you can
use following to give them access to the linked server:
exec sp_addlinkedserver N'SERVER2' , '', @.provider =N'SQLOLEDB' , @.datasrc = N'SERVER2'
EXEC sp_addlinkedsrvlogin 'SERVER2', 'true'
This will use user's current login credentials to access
the linked server.
HTH
>--Original Message--
>Hi,
>In short, the question is as follows:
>Is there any way to make server linking working without
Security Account
>Delegation enabled if the target (linked) server is set
to "Windows only"
>authentication?
>
>We've encountered the following problem:
>There are two SQL 2000 servers: SERVER1 and SERVER2.
>We want to be able to access data stored on SERVER2 from
SERVER1.
>Therefore we created on SERVER1 a linked server SERVER2
(sp_addlinkedserver
>N'SERVER2', N'SQL Server').
>If SERVER2 were set to "SQL Server and Windows"
authentication we could
>easily add a linked server login:
>sp_addlinkedsrvlogin @.rmtsrvname = N'SERVER2',
@.useself = 'FALSE',
>@.locallogin = <some local login>,
> @.rmtuser = <SERVER2_SQLLogin>, @.rmtpassword =<password>
>However, SERVER2 is set to "Windows only" authentication
and we cannot
>change it.
>How can we establish a link?
>Many thanks for any help.
>Best regards,
>Andrew
>
>.
>sql

does not violate 2nf

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)