| Author |
Topic |
|
Daz
Starting Member
5 Posts |
 Posted - 2010-02-02 : 05:56:16
|
| Hello, great site by the way, a good archive of uesful info.I have SQL Server 2008 on server 2008 sp2 and I am trying to execute a distributed query on a remote MSACCESS database (XP sp2 PC).This is causing an authentication failure (which I expected) because the SQL server requires the correct rights to the linked DB.My problem is I am not clear on how to set up the correct authentication.I have set "environment variables" on the remote PC (XP sp2) to C:\Temp folder (for TEMP and TMP) as I believe the SQL server requires access to write a temporary file at this location.I am unsure how to grant sql server the correct rights to this folder (or should I be modifying the environment variables on the server and not the remote PC?)Any guidance appreciated.Daz |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-02 : 07:08:02
|
| No environment variables are necessary. What you do need though is a user user in the database that allows you to do what you need. The easiest is to create a sql server user with a username and password, and then set up an odbc data source that you use from Access. Here's how to create the connection:http://www.ehow.com/how_4642566_setup-odbc-database-connection.html- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
Daz
Starting Member
5 Posts |
Posted - 2010-02-02 : 08:19:05
|
| Many Thanks - I'll give this a try this evening (I can access both server and PC remotely).Just to clarify completely - I create the ODBC data source on the XP PC (using ehow instructions) using login credentials I have created (using msdn instructions) on the server?Daz |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
Daz
Starting Member
5 Posts |
Posted - 2010-02-02 : 18:05:12
|
| Lumbago,Thanks for the help so far but sorry I'm still not getting the full picture. Maybe we are at cross purposes?The XP PC has an application writing records into a local MSACCESS DB. I want the remote server (running SQLServer 2008) to query the XP DB.I have actually had this working - I configured the XP PC to be part of the server domain using AD/DNS configuration (i.e. XP PC logged on to the server) but I can't do this with every remote PC so I need a solution that works with XP PC's that are not part of a domain hosted by the server (i.e. workgroup only). I believe (probably mistakenly) that SQLServer queries are naturally refused the ability to create temporary files (which I think distributed queries need to do) on the remote PC resulting in an authentication failure error when the distributed query runs (i.e. via a Job in SQL Server Agent).I have tried creating the ODBC connection on the XP machine but no option exists to point it to the SQL Server (but I have created an ODBC connection on th eserver that points to the XP Access DB - but this seems to be similar to the add linked server facilities in SQL Server Manager).I assume what I want to do is possible and can't believe Microsoft have made it so flaming tricky! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-03 : 06:19:23
|
Ooooh, your second sentence there made a huge difference. I was under the impression that it was the other way around...that you wanted to link Access to sql server so you could see and use the sql server data in an access application or something. This is a much more common scenario so I might have been a little "biased" when reading your requirements.The only way I know if doing it the other way around is to use OPENQUERY, but you would still need some sort of permissions on the file you need to read from I think. Try a syntax like this:SELECT * FROM OPENQUERY(YourODBCConnName, 'SELECT * FROM MyAccessTable WHERE ID > 10') - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Daz
Starting Member
5 Posts |
Posted - 2010-02-03 : 07:36:05
|
| Thanks.I'll try this and investigate setting permissions fo rthe server on the XP PC.Daz |
|
|
|
Daz
Starting Member
5 Posts |
Posted - 2010-02-12 : 05:06:48
|
| RESOLVED !!!!!This has cost hours and hours!The SQL Server Agent was set to run as "Local Account" which meant that any job requiring network access would fail (I found this whilst trying to copy the DB from the remote machine - using xp_cmdshell - the copy job would always success but the file was never copied).So my understanding of things is as follows.Instaling SQL Server automatically installs SQL Server Agent as a local account - this does not allow network authentication so any remote job will fail if this server is NOT a domain server and the remote computer is not logged onto the same domain.To allow remote access SQL Server Agent needs to be reconfigured using the SQL Server Configuration Manager (in SQL Servvice right click on SQL Server Agent, select properties and select the Log On tab - select the "This Account" radio button and enter a username and password - I used the admin account for the server - but any account with network access will do).Once the agent has restarted (which it will do following completion of the above) the JOb scheduler should run any remote access job.Hope this helps anyone struggling with agent jobs - I didn;t even think about this because I logged onto the SQL Server Manager as admin and naturally assumed that every service was running under admin rights - NOT TRUE, services run using rights set in SQL Configuration Manager.Daz |
|
|
|
|
Linked Table - MSACCESS Authentication Issue