How do I connect to external database using Excel

PlazaProp
PlazaProp used Ask the Experts™
on
How can I connect to a database at our home office from a remote location using Excel?  We have configured Excel to be able to work on the LAN but cannot get it to work from a WAN connection.  We have forwarded ports 1434 and 1433 on the firewall to the SQL server.  What we are figuring is that the Excel VB Script is not able to authenticate to the server for access to the network and there fore cannot pass the SQL login information to the database server.  We DO NOT want to create a VPN but strictly ride over the WAN connection.  

Here is the connection line that works on the LAN:

With ActiveSheet.QueryTables.Add(Connection:= "ODBC;DRIVER=SQL Server;SERVER=(ip address of server)\(database instance name);UID=sqluser;" & "pwd=(sqluser psw);DATABASE=paul"

The SQL Server is a Windows 2003 box running SQL Server Express 2005.  The clients are XP boxes with Excel 2003.  The ip address is static.

Can I add a line that passes windows authentication information? And can I make this a popup so that the script prompts the user for their personal login information?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Kindly check out the trusted connection syntax for Windows Authentication mode here:

http://www.connectionstrings.com/sql-server-2005#3

Hope this helps

Author

Commented:
Thanks for the link.  The connection string helped but I also had to figure out the proper server syntax.  since my sql server had multiple instances I had to find the specific port that instance was using.  the default instance uses 1433 but since I wasn't using the default that didn't work.  I had to use the sql configuration manager to examine the tcp/ip properties to find the port.

here is what the correct syntax looks like:
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=(servername),(tcpipport);UID=(sqluser);" _
        & "pwd=(userpsw);DATABASE=(databasename)" _
        , Destination:=Range("a1"))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial