RE: Distributed Query - Linked Servers?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Couldn’t you just import the restored tables from Server 2 into the Server 1
database (with different names) and do your inserts all from the same
database then delete the unncessary tables?



"Gerry Hickman" wrote:

Hello,

(Two servers, SQL Server 2005, Windows Server 2003, latest service
packs, same AD domain, services run with same domain user account)

One of our teams deleted some data (multiple joined tables) by mistake.
It wasn't practical to roll back the whole database to an earlier backup
because there were too many new entries, so instead I performed a
restore to a different server, pulled out the missing data, and inserted
it into new tables which have the same schema as the live database.

I now want to INSERT the data back into the live database, but with new IDs.

Server1 - Live
Server2 - Test Server with Restored Data

I didn't want to make any major changes to the system or meta data on
the live server; I thought it would be a good idea to "link" the two
servers and use Distributed Query to INSERT my data, then simply unlink
the second server, so I read the whole of this.

http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx

I created a link to Server2 from Server1 using the GUI, but it failed
the connection test due to authentication failure.

My own login has full rights on both servers (but obviously can't
perform double hop authentication by default).

I thought creating the link would be easy, but it seems I either have to
enable delegation or set up a special SQL authentication login. I don't
have any "sa" style passwords (vendor supplied database), and I don't
really want passwords sent over the network. I thought it would be
possible to do something like in IIS where you can use an "identity" to
run a process; you authenticate with your own account for the first hop,
then the identity performs the second hop.

When I started to read about delegation, it also says I'd need to
register an SPN (but doesn't say how) and further reading indicates
having to use a special tool in the Windows Resource Kit.

By this point, it's getting far too complicated...

Is there any simple solution to this, or do I need to forget Distributed
Query and instead copy the test database on to the first server?

--
Gerry Hickman (London UK)

.

Relevant Pages

  • Re: Using SSPI with Custom Authentication
    ... It boils down to moving the security perimeter away from the server. ... first database contains all of the user information; ... perform authentication, and users need to be authenticated before accessing ...
    (microsoft.public.dotnet.security)
  • Re: server authentication & ASP authentication
    ... Are the web server and DB on the same machine? ... My primary inclination would be to use platform authentication across the ... and Windows authentication to the database. ...
    (microsoft.public.sqlserver.security)
  • Create SharePoint Portal failed.
    ... One mentioned ensuring that SQL Server uses a case ... 13:55:40 Service database server is 'USDC-JOHRIV'. ... Update dbo.propertylist set DisplayName = N'Last name' ...
    (microsoft.public.sharepoint.portalserver)
  • Re: managing msde with nt authentication
    ... You can grant non-admin users access with sp_grantlogin: ... you'll also need to GRANT the user or role any database ... SQL Server MVP ... > change my application to use nt authentication. ...
    (microsoft.public.sqlserver.security)
  • Re: Authentication & Authorization advice
    ... MSDN has a good example of authentication using AD. ... If you're going to use a secure database, all you really need to know is ... server and connecting to the database or LDAP server. ... connects to the app server through a firewall to a specified port. ...
    (microsoft.public.dotnet.framework.aspnet)