We are having some commercial applications that are running on sql servers at different sites, all with sql security. The software vendor wants to copy data into staging tables on our BI server. But our BI server only support Integrated security.
But how can i say, in the definition of a linked server (at the remote machines) to swicth security system
If it is possible i could enter a fixed domain-account in that users fields, but i suppose that this field was intended for SqlServer security.
How to change login in linked server (From Sql Security to Integrated Sec)
Tod1d
Hi,
Create a login say (Remoteaccess) on both the server and give permission on the database(fullrights).
Use this remote login in ur linked server.
Then u can permorm ETL process on both the server.
The same way u can do on other servers also.
Another way is u can setup replication(but it will increase overhead).
regards
Mohd Sufian
Bob Drury
You can drop the existing linked server login mapping using sp_droplinkedsrvlogin and recreate them using sp_addlinkedsrvlogin. In the new login mapping, you can set the @useself parameter to true.
Let us know if this works!