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?