ODBC Connection Failure

I am having a problem getting the ODBC driver to connect.

I just installed VS2005 and can't connect to my SQL2000 server. It is there. I can't connect using either one of the mix mode authentication.

I can login to SQL from another pc when logged in with this user name. It has to be something on this pc blocking it.

Iam not rying to do this from a ASP web page just trying to setup a ODBC driver and I get these errors.

I get the error:" Login failed for user (null)" Not associated with a trusted connection

Thanks

Mike

I downloaded and installed manager studio and I can see the server in the network tab but still can't connect. I get the error 11001

Any Ideas?

Thanks

|||

Let's see your connection string. I would also read: http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

|||

This link was broken for a while and I could not reply.

William,

Little background. I have had this application up and running since Oct 05 with the connection string on several different pc's.

There is no problem with the connection string.

I recently had difficulty on two new PC, one laptop and one desktop. Neither could access SQL2000 running on another PC. Both could run the application and connection string locally if I copied the DB down to express. This application is a smart client app not an ASP app.

Both instances had newly installed VS2005 and SQL Express installed on them.

I could not even set up a DSN using ODBC manager. It would not connect to the SQL 2000 server.

When I tried using SQL Mgr Studio Express, it could see the SQL 2000 server but not connect to it.

I always got some kind of connection error.

Thanks.

odbc

I have moved my Sql2000 and the database to a new server. It works fine on the server but when I try to attach from a workstation odbc fails it tells me the server is unavailable.Hi,
Have you changed the ODBC driver on the workstation to
point to the new server?
Take a look at the account your using to connect to the db
on the new server? The account you are using must exist on
the new db server as well.
You could have orphaned users?
Possible that your new server is NOT on the same network
as the old one or your workstation.
hth
DeeJay
>--Original Message--
>I have moved my Sql2000 and the database to a new server.
It works fine on the server but when I try to attach from
a workstation odbc fails it tells me the server is
unavailable.
>.
>|||Hi,
Just by putting the users name in Enterprise Manager may
not be enough. It seems like you have orphaned users. Try
these scripts one at a time from the server or a client
machine that can connect to the new server:
1.
Use database_name
exec sp_change_users_login 'report'
This scripts reports any orphaned users in the database.
2.
Use database_name
exec
sp_change_users_login 'update_one', 'login_name', 'login_na
me_in_db'
Use this script to update all users one at a time. You
should run script for each user only if script 1 reported
a result.
If you have orphaned users and 'dspusers' was a user in
the old server, it will be returned as a result of script
1.
The other thing:
Are you able to ping this new server from the workstation?
When you configured the ODBC driver, did the server name
show-up in the drop-down list of SQL Servers?
hth
DeeJay
>--Original Message--
> Hi Deejay
>thanks for getting me started , yes I changed the Odbc
driver to point to the new server.
>I have an account called dspusers that I put into
enterprise manger and checked to be sure that my server is
on the same network. The workstation will attach to the
old server by just changing the server name in odbc, for
the new server I have used the name in odbc and when it
did not work I tried the address with the same results.
Anything else I can try.
> -- DeeJay Puar wrote: --
> Hi,
> Have you changed the ODBC driver on the workstation
to
> point to the new server?
> Take a look at the account your using to connect to
the db
> on the new server? The account you are using must
exist on
> the new db server as well.
> You could have orphaned users?
> Possible that your new server is NOT on the same
network
> as the old one or your workstation.
> hth
> DeeJay
>
> >--Original Message--
> >I have moved my Sql2000 and the database to a new
server.
> It works fine on the server but when I try to attach
from
> a workstation odbc fails it tells me the server is
> unavailable.
> >.
> >
>.
>|||Yes I can ping the server from the workstation, by name or by address. Script 1 shows all my users but dspusers, I am having trouble with script 2 getting syntax errors|||If Script 1 is returning results, all those users are
orphaned users. Orphaned users WILL NOT be able to connect
this database. I am wondering why dspusers in not showing?
Was this user already on the new server?
Script 2 should look like this:
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
Where Mary is the name currently in the db and 'NewMary'
is the new name added in the Enterprise Manager.
I need some more info:
Was the dspuser in the old server as well? Or did you just
create it in on the new server? If you created on the new
server, did you grant dspusers access in the database.
Also, look in the db users and see if name is there? To do
this: Expand the db in question, and click on users.
How did you move the database?
I am sure if mentioned already or not, are you able to
connect to the database from the server? or anyother
workstation?
You did not answer the other question: When you configured
the ODBC driver, did the server name
show-up in the drop-down list of SQL Servers?
DeeJay
>--Original Message--
>Yes I can ping the server from the workstation, by name
or by address. Script 1 shows all my users but dspusers, I
am having trouble with script 2 getting syntax errors
>.
>

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.

octo-processor with SQL2000

Can SQL Server 2000 take advantage of the processing power of a
quad-dual-core server?
Thanks,
PeteSQL2000 32 bit Enterprise can utilize 32 processors so I don't see why 8
should be an issue. But if you are getting fancy new hardware why not
upgrade to 2005 anyway?
--
Andrew J. Kelly SQL MVP
"Pete Certo" <peter.certo@.comcast.net> wrote in message
news:etfPgqlJHHA.4960@.TK2MSFTNGP06.phx.gbl...
> Can SQL Server 2000 take advantage of the processing power of a
> quad-dual-core server?
> Thanks,
> Pete
>

Occasionally slow to connect to a sql2005 database

Hi

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Hi

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R

Occasionally slow to connect to a sql2005 database

Hi

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Hi

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R

Occasionally slow to connect to a sql2005 database

Hi

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Hi

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R

Occasionally slow to connect to a sql2005 database

Hi

We have a VB app talking to SQL 2000 and SQL2005 databases. Most of our clients use SQL2000, but the new ones we are deploying with SQL2005

Sometimes the app is slow in establishing a connection to the database when it is SQL2005. The provider is sqloledb. We never get this delay in SQL2000. Sometimes it takes seconds to establish a connection - this is even the case when the app is on the same machine as the database.

We are using SQL authentication - always with the same connect string.

Any ideas ?

thanks
Bruce

Hi

Any chance of a reply on this one ?

Thanks

Bruce

|||

I have the same problem, Win32::SqlServer perl-module to connect to MS Sql Server 2005 in some cgi-scripts.

They run on the same server as the DBMS.

It must be related to the authentication method -- it is slow when the username/password is set explicitly and hence SQL authentication is forced.

If I run the scripts manually with Integrated Security (default) it connects in no time (give or take a few microsec.). Unfortunately this is not possible when run through IIS which I guess is because it doesn′t have the required permissions.

So... is it possible to give permission on a file-basis (in this case a cgi-script) so that an IIS-initiated process can access MS Sql Server with integrated seciruty?

This may not be the solution to original posters problem but it might give a hint in the right direction.

|||Same here, VB app connects to SQL Express using SQL Authentication. Using an identical connection string each time I get intermittent occurence of this slow/not responding symptoms.

It can sit and wait for a simple query to execute for maybe 30 secs or more in many cases. I had thought the Visual Studio Process had been hogging resources but this has now occured in our release builds (But not as often). This problem is replicated across our development machines.

AutoClose is set to False
AutoShrink is set to False

The query has only failed to execute on one occasion, unfortunately on a customers machine where presumably the query had timed-out. In most other cases the query has executed fine but after an impossibly long wait.

Is the server idling? If so how can I best test for the idle condition or alter the parameters for idle condition.

Cheers
R