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?
This seems like a bad idea and way to approach this solution.
First up, if the client launches Access as exclusive then Access or SQL server will not be able to successful link.
And you have to ensure that SQL server does NOT attempt to open (link) exclusive to the client side access database. Perhaps when you tested from SSMS, you did not have a copy of access launched with the same accDB file open. I not looked close, but I believe that default for the server link to Access is exclusive (that means client side accDB file cannot be opened while Access client has the same file open.
Worse, if you update any data via SERVER side in the client, then any row will be pulled to sql server, updated, and then sent back down the wire. Why not move that update code local and you NOT generate any network traffic to achieve that update on local data.
Such updates over a “wan” as opposed to a local LAN are VERY slow. While you are using the SQL system to update, you are in fact “raw” reading data from a local file, and that is a VERY bad idea over a WAN connection. If you suffer a break in the connection, you going to damage and blow up the local accDB file.
Linked tables from Access to sql server are fine and since the “file” is not being read or traveled over the network. However, linking SQL server to the local client accDB means that the windows file system is traversing from SQL server over that VPN/WAN connection. You “really” want to avoid this. This means SQL server is executing file open, and the widows file buffers and reading occurs server side. The ole jet/ace engine is running server side, but the file read is coming from client side.
I explain why this is a big “corruption” issue in this article of mine:
http://www.kallal.ca/Wan/Wans.html
Furthermore, the “model” and “idea” of linked servers is not really a model for “jump in and out”. So linked servers are more of a “maintains” type of process in which you setup a linked server and it kind of remains in place.
So some multi-user system in which linking of other serves (or an access file) on the fly is not really the idea and concept behind linked servers.
It also not clear what will occur when you have more than one user for such a system? I guess different named linked servers could be created, but then again, your t-sql code has to operate on “many” different linked servers. (so that code will have to be changed).
This means your t-sql ether has to work for “many” different linked servers for the same t-sql code, or you always use the same name for the linked server. If you use same name, then one client would be able to operate.
The more I think about this approach, the worse it gets.
Seems to me, a far smoother approach would to have Access have linked tables to SQL server.
If you’re using domain auth to connect to sql server then any user with say Excel can rather with “ease” do some quires against that sql server while you VPN is active. Same goes if they fire up SSMS.
I would consider:
Use a SQL server logon and thus auth users can’t pull or view data from those tables with Excel, Access, or some other client program that can hit SQL server with ease (and that includes a local client running SSMS).
Do NOT include the UID/password in the linked tables. What this means is you don’t have to include (in plain text) the uid/logon you use for this process.
It not clear if the Access application “hides” the access interface, but even if for some reason a user by-passed your application interface, they would receive an error by attempting to open an existing linked table. (The links would not work until such time your VBA code executes a simple logon). Having the client “logon” does not require creating of any new links client side (nor server side for that matter).
I would strong consider having the client side do/make the connection, and I would attempt to have the update of local client tables occur with client side code to eliminate the network traffic, but most worse is that “open” windows file over the network. If that file is “open”, and your connection is lost, then you cutting an open windows file “live” and simple things like flushing the local disk cache etc. will fail and be lost.
If you use links from the client side, then you never opening a windows file across that network (only a socket ODBC connection).
Just like a access application split into a front end, and back end can work well on a solid local LAN, such a setup works very poorly over a WAN + VPN.
In summary: You can likely get your setup to work, but I don’t recommend it.
What to look for:
Check for exclusive open/use of the accDB file (make sure access is not launched or opening on the client side the accDB file as exclusive). If this occurs, then sql side will not be able to make a link to the file.
Check and make sure that when creating the sql link to accDB file from that sever side, that sql server not attempting “exclusive”. (this will mean that Access local cannot open or use the file).
The fact that we already “checking” both ends for something that will cause this setup to fail is already a failure point and something we (you) are having to ensure is done right.
As noted, I don’t recommend opening a windows file across such a network.
So check the exclusive issue (both ends). However, I would look at having Access make the connection client side, and execute the sproc. If some data is required for the final update and checking that occurs in that sproc, then do the checking server side, pull the resulting required data to the client, and then execute local code to do the update.
So while I don’t think this setup will be reliable, off the top I would check the “exclusive” issue – both ends need to avoid opening the accDB file as exclusive.