asked on

DSN Connection Successful but Cannot Query from Excel

We have a large network with many different vlans, and have around 20 databases on various servers. One of these servers is on a subnet that is not on a vlan, but is interfaced to one of our access vlans by a cheap Netgear soho router.

It is necessary to query from a number of databases on this server, so I put the server in the DMZ zone on the soho router.

I can successfully establish a connection when setting up a DSN from a computer on our access vlan, but cannot establish a connection via an Excel ODBC connection.

I receive error 67 and 17, server not found or access denied.

Any ideas why I am unable to connect only through Excel?

Bryan Butler

What connection string are you using?

Tercestisi

ASKER

I am unsure of how to enter in a connection string into Excel; I know how to edit it once the connection has been made within Excel, but not how to simply enter one in without going through the data connection wizard.

This is what I would use:

Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=sqluser;Initial Catalog=DC2_0;Data Source=DATABASE;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MP7CN-MP3MM;Use Encryption for Data=False;Tag with column collation when possible=False

I have tried to create a file based DSN connection as well and open that from within Excel, but upon entering the username and password I get the same error; the connection tests fine under data sources within the OS though.

ASKER CERTIFIED SOLUTION

Tercestisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tercestisi

ASKER

Dropped instance name.