1

Topic: Adjustment Linked Server.

Kind time of days!
Is MS a SQL Server 2008 R2, during age-old times adjustment of a link with basis  has been made, probably swore on access to meta data and unwinding adjustment allow in process is made. Problems especially was not, but one of these days it was necessary to alter  the data on  procedure, which  a certain error and   process of the SQL Server . Article is found in general, which describes adjustment of these rights to remove a daw in adjustment of the provider.
However now a certain difficulty at reversal to Linked Server users, namely - works only with rights ServerAdmin. Otherwise an error "Cannot create an instance of an OLE DB provider" OraOLEDB.Oracle "for linked server". How correctly to grant the rights to OLE DB Provider, but not to give SA?

2

Re: Adjustment Linked Server.

denis_viktorovich;
https://stackoverflow.com/questions/144 … tication-u
The second answer, where MSDAINITIALIZE

3

Re: Adjustment Linked Server.

The critic;
Thanks, it just I saw. I was confused with point 18.

wrote:

Find the "OraOLEDB.Oracle" provider in a SQL Server. Make sure the "Allow Inprocess" parameter is checked.

If "Allow Inprocess" to make again is checked the error in   again can stop SQL Server service MS?

4

Re: Adjustment Linked Server.

denis_viktorovich wrote:

the Critic;
Thanks, it just I saw. I was confused with point 18.
it is passed...
If "Allow Inprocess" to make again is checked the error in   again can stop SQL Server service MS?

The colleague, and what if to go a bypass way? We admit, you from Oracle need to preempt in MSSQL a database at one go not so much. It is possible to deliver SQL Express x64 to the second  on the server, there to preempt the data from Oracle with included "allow in process", and further therefrom to preempt already in bases of the core . The service  Express stops - and it is not terrible, it is possible to launch it again.
[spoiler]
To run with the hare and hunt with the hounds, and the shepherd dug nearby smile
[/spoiler]

5

Re: Adjustment Linked Server.

denis_viktorovich;
Not-not, again to put a daw - superfluous;
At us too  service, therefore we refused this daw;
Particulars I do not remember, only keyword MSDAINITIALIZE)
So try:
https://blogs.msdn.microsoft.com/dataac … -provider/

6

Re: Adjustment Linked Server.

Sorry, it was tangled and entered all into error. Similar matter is not in the rights. Any registration with SQL authentification normally can address with  LinkedServer, even if at it the right public. If I domain  give in basis DBO, on server SERVERADMIN, SYSADMIN + is included in a role "Distributed COM Users" I all the same cannot ' create an instance of an OLE DB provider "OraOLEDB.Oracle" '. Here it to me is not clear, in one of bases on NT-shnym  access is organized.

7

Re: Adjustment Linked Server.

denis_viktorovich;
To take away on MSSQL from Oracle through Linked Server - a slow method.
Not  God and who will be after you, make as it is necessary through ETL. For SSIS use Attunity 64x and in some flows if the data many to swing.

8

Re: Adjustment Linked Server.

Alex_496 wrote:

denis_viktorovich,

so if it was possible to lead all SSIS to a packet.
In our basis there is a certain data set (a dial-up of objects which  the user and  as a whole called,  "my plans for 2020"). On each object I should to the data from our basis to tighten the Oracle data. Sampling of Oracle represents start of a train of procedures with parameters, obtaining  of session and sampling on it from the total table of result.

9

Re: Adjustment Linked Server.

Andy_OLAP;
Interesting variant, only outswapping not single and not the regulated. Will stick some persons simultaneously in arbitrary time.
P.S. While altered the client for a call of basic procedure on behalf of SQL th . To return daw Allow inprocess not a variant since on same  there is a big basis ( OLTP) and its falling is inadmissible.

10

Re: Adjustment Linked Server.

denis_viktorovich wrote:

Andy_OLAP;
Interesting variant, only outswapping not single and not the regulated. Will stick some persons simultaneously in arbitrary time.
P.S. While altered the client for a call of basic procedure on behalf of SQL th . To return daw Allow inprocess not a variant since on same  there is a big basis ( OLTP) and its falling is inadmissible.

So if will "stick" simultaneously - there is a procedure on MSSQL the core with the removed tick, she "looks" at procedure-cap transit on SQL Express with the delivered tick, and there already "looks" at Oracle.
I such would try.

11

Re: Adjustment Linked Server.

And all the same, it is interesting to understand and understand. The linkovannyj server, in adjustments it is specified incorporate on the general login and the password. Any user with SQL authentification can launch procedure on a remote server, even if at it only only Public. The domain user with NT-autentifikatsiej even with role ServerAdmin, Sysadmin receives "Cannot create an instance of an OLE DB provider..." That does not suffice the second for normal operation? In what side to dig?