Within the local area network (LAN), the SQL server agent read access Shared file error. Cries for h-CodePudding

Within the local area network (LAN), A, B two computers, computer B read table data in A computer to access the file
A and B are equipped with SQL server2014

Computer communication, A, B and B computer already get the Shared access file permissions completely

Execute the following statement:
The delete from newtable
Insert into newtable
SELECT * FROM OPENDATASOURCE (' Microsoft. Jet. The OLEDB. 4.0 ',
'Data Source=" \ \ 192.168.0.20 \ document \ BGKDB_GT MDB ". User ID=Admin; Password=')... LastData

Insert into BGKDB select * from newtable

An error is as follows:
11/04/2020 08:49:20, SSS, Error, 1, PC02 \ WINCC, SSS, SS, and Executed as user: NT AUTHORITY \ SYSTEM. Always initialize the data source object of OLE DB provider ". Microsoft Jet. The OLEDB. 4.0 "for linked server" (null) ". (SQLSTATE 42000) (7303) Error OLE DB provider ". Microsoft Jet. The OLEDB. 4.0 "for linked server" (null) "returned message" Microsoft Jet database engine can't open file '\ \ 192.168.0.20 \ document \ BGKDB_GT MDB', it has been opened by other users in the exclusive way or do not have permission to view the data, "the [SQLSTATE 01000] (Error 7412). The step failed., 00:00:00, 16741 2, 0,,,

1. The above statement in the query can be normal execution; 2. The above statement to replace the IP address for the local path to normal execution,

Yesterday a day didn't understand it, novice small white, cries for help

CodePudding user response:

You are creating a BI project, BI project permissions of the default user is the NT AUTHORITY \ SYSTEM, database so you also want to assign permissions to the user,

CodePudding user response:

reference 1st floor shoppo0505 response:

you are creating a BI project, BI project permissions of the default user is the NT AUTHORITY \ SYSTEM, database so you also want to assign permissions to the user,

BGKDB_GT. MDB LastData form data will be regularly updated in the file, the above statement is timing will update the data in the SQL BGKDB list,

You mean the new job in the agency, the owner choose the NT AUTHORITY \ SYSTEM?

CodePudding user response:

refer to the second floor wswqg0 response:

Quote: refer to 1st floor shoppo0505 response:

You mean the new job in the agency, the owner choose the NT AUTHORITY \ SYSTEM?

If the job owner choose the NT AUTHORITY \ SYSTEM, then it is database permissions to NT AUTHORITY \ SYSTEM,
Or your job owner directly select the database users,
Anyway the job of the owner and the user must be the same user database
CodePudding user response:
reference shoppo0505 reply: 3/f

Quote: refer to the second floor wswqg0 response:

Quote: refer to 1st floor shoppo0505 response:

You mean the new job in the agency, the owner choose the NT AUTHORITY \ SYSTEM?

If the job owner choose the NT AUTHORITY \ SYSTEM, then it is database permissions to NT AUTHORITY \ SYSTEM,
Or your job owner directly select the database users,
Anyway, the owner of the job and the database user must be the same user

What is you in this case, the database privileges which database?

reference shoppo0505 reply: 3/f

Quote: refer to the second floor wswqg0 response:

Quote: refer to 1st floor shoppo0505 response:

You mean the new job in the agency, the owner choose the NT AUTHORITY \ SYSTEM?

If the job owner choose the NT AUTHORITY \ SYSTEM, then it is database permissions to NT AUTHORITY \ SYSTEM,
Or your job owner directly select the database users,
Anyway, the owner of the job and the database user must be the same user

The owner of the job and the SQL database is the same user as shown
Don't know if you describe the meaning of
The same user will still be error
CodePudding user response:
The
reference 4 floor wswqg0 reply:
don't know if you describe the meaning of
The same user will still be error

This configuration is right, you
You see, is your LQ users have permission to read \ \ 192.168.0.20 \ document \ BGKDB_GT MDB permissions, (including the folders and files)
CodePudding user response:
reference 5 floor shoppo0505 reply:

Quote: refer to 4th floor wswqg0 response:

Don't know if you describe the meaning of
The same user will still be error

This configuration is right, you
You see, is your LQ users have permission to read \ \ 192.168.0.20 \ document \ BGKDB_GT MDB permissions, (including the folders and files)

reference 5 floor shoppo0505 reply:

Quote: refer to 4th floor wswqg0 response:

Don't know if you describe the meaning of
The same user will still be error

This configuration is right, you
You see, is your LQ users have permission to read \ \ 192.168.0.20 \ document \ BGKDB_GT MDB permissions, (including the folders and files)

reference 5 floor shoppo0505 reply:

Quote: refer to 4th floor wswqg0 response:

Don't know if you describe the meaning of
The same user will still be error

This configuration is right, you
You see, is your LQ users have permission to read \ \ 192.168.0.20 \ document \ BGKDB_GT MDB permissions, (including the folders and files)

Can this computer be open \ \ 192.168.0.20 \ document \ BGKDB_GT MDB this file contains the folder
The above statements in the query can perform normal execution
CodePudding user response:
Not in the machine permissions more troublesome, suggestion:
1. Create A B on A link to the server;
https://blog.csdn.net/yenange/article/details/9456145

On A 2. Create A job (job), and then by the A to get Access the data on the, inserted into the B.
CodePudding user response:
When you look at the SQL agent service login user, login user and the database service is not the same, the same try to login user and the database service, or try to operating system administrator,