Re: error setting up ODBC for remote sqlexpress database

Thanks for responding.

We were able to get it to work with the ODBC native client driver. I'm not
sure why that made such a difference. I didn't need the SQL browser and SQL
Server could be stopped in the client. I still have to try out a few more
thing. I'm not sure whether I can use Windows authentication with native mode
and whether I can get away with installing just a native client driver on the
client instead of SQL Server Express.

"Jonathan Psaila-Depasquale" wrote:

Hi,

Setting up a domain shouldn't make a difference. What I suggest you do
is to install Management Studio for SQL Server 2005 Express
(http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en)
in order to test whether you get communication problems that way. That
will allow us to find out whether ODBC is at all a culprit and whether
SQL Server and it's network library configurations may have something to
do with it.

Jonathan



ruth wrote:
I am using SQL Server Express and have a database on the server. I have no
trouble setting up the ODBC for the local machine. It works fine. When I try
to set it up on another computer to access the database on the server, after
the screen which asks for a login and a bit of a wait, I get error 53 on
Connection Open (state 01000) and error 17 for state 08001, access denied.

I have configured both machines for local and remote TCP/IP using the
Surface Area Configuration tool. I stopped and restarted SQL Server Express
after doing so. It didn't help. I tried rebooting both machines. It didn't
help.

The server name I am using in the ODBC setup is e.g. SERVER\SQLEXPRESS. I
also tried \\SERVER\SQLEXPRESS, where SERVER is the machine name on which the
database resides, but that failed sooner with an unknown server error.

We are using a peer to peer workgroup network, which may be the problem.
Windows NT Authorization fails, so we reinstalled SQL Express with mixed
mode, the default user (sa) and a password (sql). This didn't work either.
Same error message. We tried adding a new login and user (on the server) in
SQL Server express, but that didn't work either when setting up a remote
ODBC. All these methods worked fine locally on the server.

If SQL Browser service is turned on, we can get past the authentication
screen. (It was turned on for both client and server when this worked.) We
communicated with SQL Server and could pick up the database name. The test of
the connection failed, however, with a “Communication link failure.”

We can create a remote ODBC connection for an Access database from the same
client machine to the same server. The problem only appears for SQL Server.

Any suggestions on how to get around this or must we use a domain network?



.

Relevant Pages

  • Re: SQL Server 2005 Express OLEDB Connection from VB6
    ... Thanks for getting me to read this again and realizing they meant the client ... I think we'll stick with ODBC, since there is less to install on the client ... Microsoft SQL Server Native Client ...
    (microsoft.public.sqlserver.connect)
  • Re: Access link table security
    ... onerous chore of creating DSN's on each client. ... >and SQL Server is on the same machine. ... >How and where do I set at the Access frontend so that I can choose the login ... >change the login name for the Access frontend, do I need to play with ODBC ...
    (microsoft.public.sqlserver.security)
  • RE: Remoting - Some methods hang on certain setups.
    ... "[ODBC SqlBase driver] You are not licensed to use this Merant Driver under ... The weird part is when we run both client & server at the same location, ... The ODBC-Driver is used to query the AccountancyDb through a provided API ...
    (microsoft.public.dotnet.languages.vb)
  • Linked server to ODBC DSN
    ... Server, and want to create a linked server to this same ODBC DSN: ... Provider = "Microsoft OLE DB Provider for ODBC Driver" ... Then i connect to the SQL Server using QA, and try to run my query (that ...
    (microsoft.public.sqlserver.server)
  • Security strategy: Access client/SQL Server
    ... I will be creating an application using MS Access as a client to SQL ... SQL Server resides. ... permissions for group X to open form A, users would need to get a new ...
    (comp.databases.ms-access)