Connect to Remote SQL Database Using Excel

Connect to remote sql database using excel

There can be several reasons for which you cannot connect to your SQL Server:

A) It doesn't admit remote connections

B) There's a firewall or anything else that avoids remote computers connections

C) You don't have SQL Browser service running

Troubleshooting steps:

1) In a remote computer open cmd and run ping win2k8-01 You should get responses from your computer. if not, there's a network connectivity problem

2) Check that the "SQL Browser service" is running in your computer (Services, in Administrative tools, in Control panel) If not set it start type as automatic and start it. This allows remote computers to get information about available instances and how to connect to them. If in doubt, look here

3) Verify that TCP/IP is enabled. If not enable it How to: Enable the TCP/IP Protocol for a Database Instance

4) Verify that your server allows remote connections. To do this open SSMS, connect to your server, right click, open Properties, and look for "Allow remote connections" on "Connections" tab. get sure this option is checked. If in doubt, look here

5) Disable firewall in your computer (windows firewall or any 3rd party firewall like those installed by antivirus software)

Try connecting using the same connection configuration that worked in your local computer. It should work now.

If you cannot connect using Excel you should try connecting with another tool from the remote computer (you can install SSMS on that computer and try connecting).

This should work. if not let me know what is failing.

If it's working, enable firewall again and try connecting again. If you cannot, you need to configure your firewall to allow SQL Server connections. Do that and try again. Don't forget to allow connections to SQL Server browser on UDP port 1434, apart form the DB instance port on TCP.

VBA is unable to create a connection to a remote SQL Database

After struggling for a few days, I finally got this to work. There are two important steps you need to take to make excel work with a Remote SQL network. First here is the code used to connect:

Dim cnSQL As ADODB.Connection
Dim ServerName As String
Dim DatabaseName As String
Dim userID As String
Dim password As String
ServerName = "IP Addresss,Port"
DatabaseName = "DB Name"
userID = "username"
password = "pwd"
Set cnSQL = New ADODB.Connection
sqlCommand.ActiveConnection = cnSQL

After I changed the code to this, I got a new error: "optional feature not implemented." To fix this error, I changed the data type of all 'adDate' to 'adDBTimeStamp.' This works for some reason, I don't know why. It works.

Connect with Microsoft Excel to Microsoft SQL only works with trusted connection

Trusted_connection means the connection is using the current logged in user details and pass these on to SQL Server to authenticate with (Windows Authentication).

If you want to use SQL authentication (username and password) then don't use this flag.

Have a look through this article, might be helpful explaining the different authentication methods. https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?redirectedfrom=MSDN&view=sql-server-ver15

If you want to use windows authentication (trusted_connection flag) and don't want to individually add the users, the alternative you can have is to add users to a domain group and set that group with the correct permissions. But from reading between the lines of your question you should just ignore this flag if you want to use SQL authentication

Python - Using pyodbc to connect to remote server using info from Excel data connection

You don't need the single quotes around each field, and may need a port... Try something like this:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.123.456;PORT=1433;DATABASE=yourdb;UID=your_user;PWD=your_pw;')

Good luck!

Creating vba code to connect to a remote SQL server, executre a stored procedure that updates a table on the accdb file

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.

Excel connection to database, problem when shared in One Drive

do you realy need an SQL-Server for dataset auf 100 rows and 24 columns?
I would try do store the date in a shared Excel-File in Onedrive an connect to this.

Best regards
Chris

EDIT
If you want to connect to Private-OneDrive File you should use a WEB-Connector instead of a "File"-Connector.

Your Source should be like this:

=Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=AAAXXXXAAA&authkey=BBBXXXXBBB&em=CCCXXXXCCC&app=Excel"), null, true)

You have to replace the AAAXXXXAAAA; BBBXXXXBBB and CCCXXXXCCC with informations from the embedded-link of your XLSX-File:
1) open OneDrive in WEB
2) Select you file and right-click
3) click "embedded"
4) in the right pane you find the embedded-link
5) find there the resid; authkey and em

OneDrive GetEmbeddedLink

---

Related Topics