ODBC Connection Issue on Client Site

Hi,

It might be a basic question but could you please help me on that?

Working Environment:

I have developed application on SQL Server 2005 and now wants to Run on Client Site, I am using access as a front end and SQL server 2005 back end through ODBC (System Dsn) which is working good. (user has been added on SQL Server and on Database)

Question:

1) Do I need to create new System Dsn Or other DSN (Keep in mind I have already create System DSN in Server) at each client site to access application from Server?

(If yes then I am trying to do that, it allow me to create (with required database which is on Server) and verify but doesn’t save on ODBC list so I can’t use that name to run appliation on client site)

I am using windows authentication.

Thanks for help in advance

It's done the issue was admin rights on the client's computer.

ODBC connection for client application to SQL Server 2005 Express installed on network computer

Hi All,

I've developed an application that connects to a SQL Server 2005 Express database. I created a DSN to connect to the database through ODBC. Currently, I am testing locally and everything works fine.

I would now like to install my application on another workstation and connect remotely to the database located on my development machine.

The client workstation does not have SQL Server 2005 Express installed on it because I would just like my application to connect remotely by creating the DSN and using ODBC. What I'm missing here are the database drivers. The "SQL Natice Client" is not available on this client workstation. How can I deploy the necessary drivers with my installation file so that I may create the required DSN name using the SQL Native Client driver?

Thanks!
Deployment of the SNAC can be found here:

http://msdn2.microsoft.com/en-us/library/ms131334.aspx

If you don′t use the new features of SQL Server 2005, you can also use the MDAC driver which is compatible with the Pre SQL 2005 features of SQL Server 2005.

My best practice is not to use the DSN rather than using direct connection strings which can be configured in configuration files.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||Thanks for the reply.

I can't seem to find the sqlncli.msi file on my drive. I have the Express edition installed. Is there somewhere else I can find this installer for the SNAC driver?

If not, which version of MDAC do I need, and how would I build my DSN? I agree with using connection strings instead of DSN's, unfortunately that is a decision I'm unable to change.

|||

SNAC: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc
it

Just get the newest MDAC, since it makes no difference if you get 2.7 or 2.8 but getting the newest will keep you away from old bugs :-)

The connectionstrings can be found on www.connectionstring.com (Where I also posted an example for the SNAC one :-) )

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Excellent,

I got it working using MDAC.

Thanks!

ODBC and Enterprise Manager (still) don't show databases with users other than sa

I have several Win2000 servers running SQL Server 2000, SP2 (8.00.665)
and only one of them developed this problem. When trying to set up a
new ODBC setting using any other login than sa, it doesn't show any
databases besides the system db's. When logging in with Enterprise
manager (all other logins except sa), the Databases list shows 'No
items'. All logins work fine with Query Analyzer. I already recreated
missing guest accounts in some of the databases, but that didn't help.
I've also installed the latest MDAC (2.7 SP1) and after that didn't
correct the problem, I installed SP3a. It's still not working.
I'm thinking about reinstalling SQL Server, but my client doesn't like
the idea. So if anyone has any idea what might help, I'd be greatful.
Thank you!
br,
AnnaAnna
Firstly you need to have SQL SP3 if you have not applied the patch to stop
the SQL Slammer on the server.
Are you using ODBC/ Query Analyzer from the server or the client?
I am getting a few problems as well after install MDAC 2.71 which has
somehow deleted my ODBC drivers from the Drivers tab window.
Regards
Z
Is the SQL a named instance?
"Anna N." <amnikol@.hotmail.com> wrote in message
news:6e225598.0307160146.5fadd9f2@.posting.google.com...
> I have several Win2000 servers running SQL Server 2000, SP2 (8.00.665)
> and only one of them developed this problem. When trying to set up a
> new ODBC setting using any other login than sa, it doesn't show any
> databases besides the system db's. When logging in with Enterprise
> manager (all other logins except sa), the Databases list shows 'No
> items'. All logins work fine with Query Analyzer. I already recreated
> missing guest accounts in some of the databases, but that didn't help.
> I've also installed the latest MDAC (2.7 SP1) and after that didn't
> correct the problem, I installed SP3a. It's still not working.
> I'm thinking about reinstalling SQL Server, but my client doesn't like
> the idea. So if anyone has any idea what might help, I'd be greatful.
> Thank you!
>
> br,
> Anna|||FYI:
First, I would try determine if it is an Server or Client Problem. ( The
Server has both Client and Server DLLs on it.)
To see if it is an client problem try to make the ODBC from a machine
without the problem to the Server with the problem.
If the problem is NOT there then it is an client problem.
If the problem is there confirm it by making an ODBC connect to one of the
Good Servers from the same test client.
If it is an server problem, I have nothing to suggest.
( Note my company spent over 40 man hours on an "Server Problem" that
turned out to be an client DLL issuse in SQL SP3)
If an client problem.
First check the version of SQLSRV32.dll ; is the correct one.
Link to version of SQLSRV32.dll
http://support.microsoft.com/default.aspx?scid=http%3a%2f%2fsupport.microsoft.com%2fservicedesks%2ffileversion%2fdllinfo.asp&fp=1
Then, I would try running the component checker ( on the MDAC ).
Link to info on component checker
http://support.microsoft.com/default.aspx?scid=kb;EN-US;307255
Link to download componet checker
http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en
Tim S
"Zenduce" <james@.zenduce.net> wrote in message
news:%238XHwp4SDHA.1664@.TK2MSFTNGP11.phx.gbl...
> Anna
> Firstly you need to have SQL SP3 if you have not applied the patch to
stop
> the SQL Slammer on the server.
> Are you using ODBC/ Query Analyzer from the server or the client?
> I am getting a few problems as well after install MDAC 2.71 which has
> somehow deleted my ODBC drivers from the Drivers tab window.
> Regards
> Z
>
> Is the SQL a named instance?
> "Anna N." <amnikol@.hotmail.com> wrote in message
> news:6e225598.0307160146.5fadd9f2@.posting.google.com...
> > I have several Win2000 servers running SQL Server 2000, SP2 (8.00.665)
> > and only one of them developed this problem. When trying to set up a
> > new ODBC setting using any other login than sa, it doesn't show any
> > databases besides the system db's. When logging in with Enterprise
> > manager (all other logins except sa), the Databases list shows 'No
> > items'. All logins work fine with Query Analyzer. I already recreated
> > missing guest accounts in some of the databases, but that didn't help.
> > I've also installed the latest MDAC (2.7 SP1) and after that didn't
> > correct the problem, I installed SP3a. It's still not working.
> >
> > I'm thinking about reinstalling SQL Server, but my client doesn't like
> > the idea. So if anyone has any idea what might help, I'd be greatful.
> > Thank you!
> >
> >
> > br,
> > Anna
>|||"Zenduce" <james@.zenduce.net> wrote in message news:<#8XHwp4SDHA.1664@.TK2MSFTNGP11.phx.gbl>...
> Firstly you need to have SQL SP3 if you have not applied the patch to stop
> the SQL Slammer on the server.
I already have SP3a installed, as you can see from my original post.
> Are you using ODBC/ Query Analyzer from the server or the client?
Several clients.
> I am getting a few problems as well after install MDAC 2.71 which has
> somehow deleted my ODBC drivers from the Drivers tab window.
This problem emerged BEFORE installing MDAC 2.71, as you can see from
my original post.
> Is the SQL a named instance?
No.
- Anna|||"Tim S" <stahta01@.juno.com> wrote in message news:<XamRa.4637$C71.307@.fe01.atl2.webusenet.com>...
> First, I would try determine if it is an Server or Client Problem. ( The
> Server has both Client and Server DLLs on it.)
It is most definitely a server problem. I wouldn't have posted a
question without making sure where the problem is exactly.
- Anna

ODBC

Hey

I have developed a asp.net 2 system on my local machine using a local sql2000 server.
Now i'm trying to deploy it to a windows 2003 server, using a sql2000 server on another windows 2003 server. I thought the only thing i had to do was to create a ODBC connection to the sql server, and change the connectionstring. But no :)

What kind of changes do i need to do. Do i need to change the dataprovider.

I get this error:

An error has occurred while establishing a connection to the server. When connecting to sql Server 2005, this failure may be caused by the fact that under the default settings sql Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to sql server)

But i don't use a sql 2005 server.

Pleace help :)

When I got this error the reason was SQL Server service in SQL Server 2000 was off but I had SQL Server 2005 installed also so check to see if your SQL Server service is on. Hope this helps.|||

I can connect to the database through enterprise mananger, and if i create an ODBC connection. So i'm quite sure the service is running.

But i'm not sure how to connect to a shared sql server on another server. Do i need to use ODBC? Do i need to change some providers? How would the connectionstring look like.

Thanks

|||

No you don't need ODBC to connect to SQL Server because the ODBC standard is very old. Try the link below for connection strings. Hope this helps.

http://www.connectionstrings.com

|||

That's what i thougt to. But i can only get my application to work when using my local sql server. If i try to use a sql server on another server i get this error.

But is this correct:

Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

Where Aron1 is servername(not sql server name)??
Pubs is the database.

Is this enough information?

Thanks for your help.

|||

Aron1 is the SQL Server instance name and all the information you need is in that site but if you need to know more about connection and SQL Server ANSI SQL permissions try the link below for a Microsoft provided tutorial. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||

An error has occurred while establishing a connection to the server. When connecting to sql Server 2005, this failure may be caused by the fact that under the default settings sql Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to sql server)

Change to the TCP/IP Provider and see if your problem goes away.

|||

Found my problem. A firewall on the server was blocking this kind of traffic.