Sorry about the text, trying to be concise
We are trying to set up a process to have our end users (remote users on laptops that move between different networks) who are using access databases, send a request through to our SQL server to have that server execute a stored procedure. Once this request is received at the SQL Server, the server should look up a series of expected values for that user, then execute a different stored procedure to initiate a new link back to the calling device (based on how it is connected to the internet this time), run a series of validations on the data on that machine, and if the validations are successful it should update data in a table on the remote client, then disconnect.
We have the calling procedures set up and working just fine when they are executed from the SQL server directly using SSMS (e.g.: we log into the server running the SQL server, and then use SSMS to execute the stored procedure), and if we leave the links there we can use SSMS to read/edit/modify any of the data/tables in the remote client. However when stored procedures are called from the remote client they work, unless those stored procedures try to read/write to the remote linked server (e.g.: the stored procedures which add and remove the links to the database work fine, but the queries that look at or edit the data in the remote database return an error that looks like it is related to permissions relating to file access).
Our Environment:Devices are on our Domain, SQLserver 2017 Express, Windows 10, accdb database (2007)
In Summary: We am writing VBA code to execute a remote SQL Server 2017 Express stored procedure on our domain to update a table within the same calling accdb database (2007) running on a windows 10 computer. Outcome: We want the remote machine to only run stored procedures on the sql server not have specific tables/views both for the added security and the efficiency. Ie the access vba says im online, sql server create link and checks for updated contacts, downloads and disconnect. procedure creates a LinkServer (sp_addlinkedserver), Logs in(sp_addlinkedsrvlogin), updates a table (@sql = 'UPDATE ') , deletes link (sp_dropserver) Once we have this working we plan to create other stored procedures that query the accdb file and upload new data to the SQL server. We are blocked when attempting to “SELECT” or UPDATE” to the remote access file via the stored procedure in VBA (the stored procedure works fine from the SSMS interface).
So, on the SQL server we currently have
CREATE PROCEDURE [dbo].[SQLserverlastconnect] @p_linkservername nvarchar(max) = null, @p_linkdatasrc nvarchar(max) = NULL
AS
DECLARE @sql NVARCHAR(MAX)
EXEC master.dbo.sp_addlinkedserver @server = @p_linkservername, @srvproduct=N'', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc= @p_linkdatasrc
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'<servername>\<instance>', @locallogin = NULL , @useself = N'True';
SET @sql = 'UPDATE ' + @p_linkservername + '...tbl_Ref_Local SET tqs_Data = ''' + CONVERT(varchar(50),FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd hh:mm:ss')) + ''' WHERE tqs_KeyIdentifier = ''LastConnect'''
EXEC(@sql)
EXEC master.sys.sp_dropserver @p_linkservername,'droplogins'
In Access 365 we have a Sub (tried many changes to this including looking for issues with the connection string)
Sub Test()
Dim connection As Object: Set connection = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
With connection
.ConnectionString = "DRIVER=SQL Server;Server=<servername>\<Instance>;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=SixBit;User ID=sa;Password=********" ‘[we tried to remove User ID/Password and only use a trusted connection]
.CommandTimeout =0
.Open
End With
Set rs = connection.Execute("EXEC dbo.SQLserverlastconnect @p_linkservername = N'Test7', @p_linkdatasrc = N'\\fileserver\Programming\TQS - Client 0.03.22.accdb'")
‘tried this also Set rs = connection.Execute("INSERT INTO [Test7]...[tbl_Ref_Local] ([tqs_KeyIdentifier],[tqs_Data],[tqs_GUID]) VALUES ('t', 't' ,'t')")
connection.Close: Set rs = Nothing: Set connection = Nothing
End Sub
Things We Tried: We have added Everyone to the network share where the file resides, added everyone to the temp path, Changed the SQL server service ‘Log on as’ from the default to a domain admin and also “Network Service”. One area we have not spent as much time is the “login mappings” on the Linked Server Properties on the SQL server (we did try impersonating ‘sa’) Here is the current message we get when running the VBA. Tried setting up a NET USE U:, tried pointing to another access database (older version) that is not open as the message appears to imply we cannot open the database while it is running, also tried a text file.
Lastly, we created a trigger and can get this to run a SELECT (much fist pumping), but not an UPDATE or INSERT(sad face) but this may indicate something to someone out there.
Has anyone got any advise?
