February 8, 2017 at 10:15 am
Hi All,
So we have 2 instances of SQL Server on the same machine.
One is installed to another drive not C drive.
We have set up a linked server on one instance and it works fine for SQL auth users.
Set up the same linked server with the same credentials on the other instance. I can see it under my domain account with SA rights, but when I set up a SQL auth user only, I get the below error:
Cannot initialise the data source object of ole db provider msdasql for linked server microsoft sql server error 7303 .
I am working out it's permissions. I have set the linked server and permissions as I did the other one, but instead of seeing a folder called SF that contains the tables it says default then throws up that error.
The 2 instances have different NT Service\ accounts.
Any pointers? I have googled but it all talks about querying the data. I can't currently see the tables :). I am assuming permissions but abit stumped on where to look.
Thanks in advance!
February 10, 2017 at 4:24 pm
Kazmerelda - Wednesday, February 8, 2017 10:15 AMHi All,So we have 2 instances of SQL Server on the same machine.
One is installed to another drive not C drive.
We have set up a linked server on one instance and it works fine for SQL auth users.
Set up the same linked server with the same credentials on the other instance. I can see it under my domain account with SA rights, but when I set up a SQL auth user only, I get the below error:
Cannot initialise the data source object of ole db provider msdasql for linked server microsoft sql server error 7303 .
I am working out it's permissions. I have set the linked server and permissions as I did the other one, but instead of seeing a folder called SF that contains the tables it says default then throws up that error.
The 2 instances have different NT Service\ accounts.
Any pointers? I have googled but it all talks about querying the data. I can't currently see the tables :). I am assuming permissions but abit stumped on where to look.
Thanks in advance!
On the same server, one works and one doesn't. That makes me wonder if the one your are having problems with is a named instance and it's just not being referenced correctly in the connection string? I'd double check the login and password being used since that can throw the same error with that provider.
Any reason why your using the OLE DB provider for ODBC for this? MSDASQL is becoming obsolete, I would guess it's being deprecated if it isn't already. I think it was the first OLE DB (or one of the first) provider and was written for ODBC because few data sources exposed or supported OLE DB so this was a translater to go through ODBC. So it's pretty old and not likely to support everything.
Sue
February 13, 2017 at 9:11 pm
This was removed by the editor as SPAM
February 14, 2017 at 5:46 am
Thanks both, the source is using that connection as advised by the company but I will take a look at that.
JasonClark, I have given access to the appdata\local\temp for both of the ID folders to everyone but still no joy.
Oddly I have tried a Windows auth ID with basic permissions. Can't see the linked server tables. Add in sysadmin, bingo! There is no way I am giving these users sysadmin so will keep battling on.
Thanks for your help, always good to know the thing you have tried are right so far!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply