November 3, 2004 at 11:28 am
I am fairly new to SQL Server and working with linked servers, so any help would be great.
Here is the situation:
1. We receive a data file that is a fixed length text file: datafile.txt
2. SQL Server 2k is running on: ServerA
3. The users who generate the file are not allowed to work on ServerA.
4. They must place the file (datafile.txt) on ServerB.
5. The location is: \\ServerB\Adirectory
6. A schema.ini file is in \\ServerB\Adirectory defining the structure of datafile.txt.
7. I need to create a linked server to the text file on ServerB from ServerA.
8. The SQL Server Agent uses a Windows login (ServiceLogin) and is able to work on the network.
9. The two servers and the Windows login are all in the same domain using ActiveDirectory: OurDomain
10. Here are the current linked server setting:
Server Type:
Other data source
Provider Name: Microsoft Jet 4.0 OLE DB Provider
Product Name: Jet 4.0
Data source: \\ServerB\Adirectory
Provider String: Text
11. I have a local login defined in SQL Server: OurDomain\ServiceLogin
My problem is that I can’t seem to get the security setting correct. I tried mapping the local login(OurDomain\ServiceLogin) to:
Remote User: OurDomain\ServiceLogin
Remote Password: ********(the Windows password)
This doesn’t work. I think that Remote User and Password refer to what would be a SQL Server login on another SQL Server.
There error message I keep getting is:
Error 7416: Access to the remote server is denied because no login-mapping exists.
If I manually place the files on ServerA I can get the linked server to work, but I don’t want to have to do this every time the file is updated.
Is there some way that I can use the SQL Service Agent to access the data file. The ServiceLogin user has right/permissions to ServerB.
How is SQL Server attempting to connect/logon/authenticate to ServerB. Is there something else that I am missing?
November 3, 2004 at 11:36 am
If I were you I would either write a DTS package or I will copy that text file to the server where SQL server is located. You can either set up a ftp directory where you can copy the file using an NT job from server A to B or B to A whichever you require. Since the file is on the same server where the SQL is, it will make your job much easier on the SQL Side.
Hope this helps.
-Nitin
November 4, 2004 at 8:07 am
Hi,
I've had this problem with the jet driver when creating linked servers to excel files. It would only work when the file was on a local drive. Since you connecting to a text file, I would try using the text ODBC driver and then connect with the OLEDB provider for ODBC.
November 8, 2004 at 1:25 pm
I agree that DTS might be the better way to go. I have had similar issues linking to Excel on another server. I believe it has to do with Windows authentication between servers.
Look in SQL BOL for "Security Account Delegation". It talks about having Kerberos support enabled on your servers in order for the remote server to retain the authentication credentials of the original client.
It does not appear to be an easy thing to accomplish and I have not done it. I use DTS instead.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply