December 1, 2011 at 9:24 am
I ask again, only because I cannot figure this out... Are you using the same SQL Server (windows auth) user to log into the database instance between the server and your laptop? It has to be a permissions issue with the user as far as I can tell...
http://blogs.msdn.com/b/john_daskalakis/archive/2010/02/01/9956271.aspx i.e. When you log into the server are you using the EXACT same credentials that you use to log into your laptop? If you use windows authentication to log into SQL and the machine logins are different, you will have different permissions. For example, you may log into your laptop as domain\username, but log into the server using only username. Those are 2 different SQL logins and may have different permissions.
Jared
Yes I am using the same exact windows authentication user to login from both places (laptop as well as database server) and I use the same username and password to log into the laptop.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 9:26 am
configuration_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced
16391Ad Hoc Distributed Queries0010Enable or disable Ad Hoc Distributed Queries11
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 9:27 am
p-nut (12/1/2011)
I ask again, only because I cannot figure this out... Are you using the same SQL Server (windows auth) user to log into the database instance between the server and your laptop? It has to be a permissions issue with the user as far as I can tell...http://blogs.msdn.com/b/john_daskalakis/archive/2010/02/01/9956271.aspx i.e. When you log into the server are you using the EXACT same credentials that you use to log into your laptop? If you use windows authentication to log into SQL and the machine logins are different, you will have different permissions. For example, you may log into your laptop as domain\username, but log into the server using only username. Those are 2 different SQL logins and may have different permissions.
Jared
Do you have SQL Server Authentication enabled as well? i.e. can you log into ssms on your laptop to the database instance needed with sa and is it any different?
Jared
Jared
CE - Microsoft
December 1, 2011 at 9:27 am
The value, minimum and value_in_use columns are 0's
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 9:30 am
Sapen (12/1/2011)
The value, minimum and value_in_use columns are 0's
Well... You should start here:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Jared
Jared
CE - Microsoft
December 1, 2011 at 9:35 am
No..I am not able to access any linked server unless I create the linked server connection directly on sql server installed on my laptop.
I am getting a little confused. You have a Source SQL Server and a Target Providex server. Where does your laptop fit into this? Is your laptop running a full blown SQL instance or do you just have the Client tools installed (meaning when you start up SSMS on the laptop, do you connect to your "local" instance, or do you connect to the Target SQL Server?)?
If you have SQL Server installed on your laptop and it's running it's own instance, you'll need the linked server created on there as well. In addition, if the Providex server requires a specific ODBC driver, I highly reccomend you create a System-Level ODBC driver (entering in all the appropriate information there), then creating your linked server on your laptop to be a "Microsoft OLE DB Provider for ODBC Drivers"
?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 1, 2011 at 11:54 am
Sapen (12/1/2011)
The value, minimum and value_in_use columns are 0's
Well... You should start here:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Jared
Edited: Today @ 4:30:44 PM by p-nut
I just tried this. Do I need a service restart? coz I still dont see the difference.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 11:58 am
I don't think you should have to restart the service after that. I still think there is a permissions issue. Something in your domain is not interpreting the user the same between your 2 machines. You are sure both the server AND your laptop are on the domain? I just can't see it being anything other than permissions.
Jared
Jared
CE - Microsoft
December 1, 2011 at 11:59 am
No..I am not able to access any linked server unless I create the linked server connection directly on sql server installed on my laptop.
I am getting a little confused. You have a Source SQL Server and a Target Providex server. Where does your laptop fit into this? Is your laptop running a full blown SQL instance or do you just have the Client tools installed (meaning when you start up SSMS on the laptop, do you connect to your "local" instance, or do you connect to the Target SQL Server?)?
If you have SQL Server installed on your laptop and it's running it's own instance, you'll need the linked server created on there as well. In addition, if the Providex server requires a specific ODBC driver, I highly reccomend you create a System-Level ODBC driver (entering in all the appropriate information there), then creating your linked server on your laptop to be a "Microsoft OLE DB Provider for ODBC Drivers"
?
Let me put it this way...I have configured linked server on my SQL Server (say A) and it is linked with Mas90 Providex db (say B). Here A and B are different servers. Now when I login to A and connect to sql server and run my select query, I am able to fetch the data. Now instead of loging on to the Server A and connect to SQL Server what I am trying to do is connect to the same sql server from my laptop with the same authentication(windows) and run my select query and I get the below error message:
OLE DB provider "MSDASQL" for linked server "mas90_link" returned message "[ProvideX][ODBC Driver][FILEIO]Table is not accessible".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table "ar_customer" from OLE DB provider "MSDASQL" for linked server "mas90_link".
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 12:45 pm
I don't think you should have to restart the service after that. I still think there is a permissions issue. Something in your domain is not interpreting the user the same between your 2 machines. You are sure both the server AND your laptop are on the domain? I just can't see it being anything other than permissions.
Jared
Yes..they are all in the same domain.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 1, 2011 at 12:56 pm
Sapen (12/1/2011)
I don't think you should have to restart the service after that. I still think there is a permissions issue. Something in your domain is not interpreting the user the same between your 2 machines. You are sure both the server AND your laptop are on the domain? I just can't see it being anything other than permissions.
Jared
Yes..they are all in the same domain.
Well, I'm sorry but I am out of ideas. It seems to me it is a permissions problem, but at this point I cannot point to where.
Jared
Jared
CE - Microsoft
December 1, 2011 at 1:03 pm
I'm with Jared. Clearly a permissions problem but am out of ideas :crazy:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 19, 2012 at 8:49 am
Did anyone ever figure anything out with this? Im having the exact same issue.
I connect to server A running Sql and can execute my stored procedure that has a link to mas90.
I connect to server B that runs a job that connects to server A using the same login credentials. This job basically just executes the same Stored Procedure as above...This option fails with "Table not accessible". I have set up the security contexts with user/pass that allows me to get into mas90.
Im sure its some sorta thing where MAS90 isnt allowing remote connections, but is this something that needs to change on mas90 or SQL?
i'd love to know if anyone solved this.
July 19, 2012 at 11:10 am
andrew.davies 84954 (7/19/2012)
Did anyone ever figure anything out with this? Im having the exact same issue.I connect to server A running Sql and can execute my stored procedure that has a link to mas90.
I connect to server B that runs a job that connects to server A using the same login credentials. This job basically just executes the same Stored Procedure as above...This option fails with "Table not accessible". I have set up the security contexts with user/pass that allows me to get into mas90.
Im sure its some sorta thing where MAS90 isnt allowing remote connections, but is this something that needs to change on mas90 or SQL?
i'd love to know if anyone solved this.
If your server B is having a link with Mas90 then ensure that the sql version you are using is 32Bit or your linked server wont work.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 19, 2012 at 12:18 pm
did that end up being the problem? One is 32 bit and its trying to run a linked server against a 64bit version....BUT....it has no problems running other stored procedures on the same SQL server..
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply