|
Posted by TR on March 14, 2008, 10:23 am
Please log in for more thread options
cross-posted to sqlserver.security and windows.server.security
We want to use legacy data in a 'heterogeneous join' with current
production data on our SQL Server. One of our Windows servers hosts a
legacy Access 97 MDB secured by MDW, and another server hosts an
instance of SQL Server 2000. I also have an instance of SQL Server 2000
running on my development PC.
On my development PC, I created a DSN pointing to the secured MDB. Then
I run a query in Query Analyzer, using OpenRowset with the DSN, and it
works. The legacy data does come across and can be joined with SQL
production data. NOTE: I connect to the local SQL instance running on
my development PC using Windows Authentication. My Windows user identity
has been granted file-system permissions to the legacy Access97 data
directory.
On the SQL production server, I create a DSN in the same way using the
ODBC Connections wizard. All the information is the same. Then I go
back to my development PC and connect to the production SQL instance
with Query Analyzer, only this time I use SQL authentication instead of
Windows authentication (as my users will), but the OpenRowset query
fails with an database initialization error.
Am I correct that when the OpenRowset('MSDASQL',MyDSN ...) query is run
using SQL authentication on the production server, the owner of the
SQL instance on the production server requires Windows file-system
permissions on the legacy data pointed to by the DSN? If yes, how do I
identify and specify "Local System on Another Server" in the
file-permissions dialog?
Thanks!
TR
| 
XML Sitemap