SQL Server - Unable to create DSN

Asked By Ramya M. on 16-Dec-08 05:54 AM
Hi All..

I m using Visual Studio 2008 (C# .Net) for my project.. and i m using SQL Server 2005 as DataBase....

i m trying to create user DSN.. but i m getting the below error....

first, i will explain u the steps i m follwoing to create the DSN...

control panel==>
    Administrative Tools==>
        Data Sources(ODBC)==>
             in 'ODBC Data Source Administrator' Window, go to Add...
 then i m selecting SQL Server==>
                                       Finish.
then i will give the DSN Name as Sal1 and i will select the server name as '.\sqlexpress' ==>Next in next window, i m selecting 'With Window NT Authentication using the network login id' Radiobutton
and i m selecting the 'Connect to SQL Server to obtain default settings for the additional configuration options' checkbox.... Till here it is working fine... but when i click 'Next' button, i m getting the below error....

connection failed:
SQL State:'01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection open(Connect( )).
Connection failed:
SQL State:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

Note:  i m getting the same error for already created DSN which i was using for some othr project...  befor this DSN was working fine... but now if i select this DSN and if i give 'Configure'. i will get the similar error:

connection failed:
SQL State:'01000'
SQL Server Error: 11004
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection open(Connect( )).
Connection failed:
SQL State:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

can anybody help me??????? please....
 

reply

Binny ch replied to Ramya M. on 16-Dec-08 06:35 AM
Try it this way:
control panel==>
    Administrative Tools==>
        Data Sources(ODBC)==>
          System dsn==> Add===>Sqlnative===>
           
Enter the datasource-->Server---->Next-->
Finally click Test connection

You can use an OLE DB provider to communicate

Venkat K replied to Ramya M. on 16-Dec-08 01:02 PM

with your database. Creating a direct database-specific OLE DB connection can improve the speed of your connection by eliminating the ODBC layer between your web application and the database.

If you don't specify an OLE DB provider for your database, ASP uses the default OLE DB provider for ODBC drivers to communicate with an ODBC driver, which in turn communicates with the database.

Different OLE DB providers exist for different databases. You can obtain OLE DB providers for Microsoft SQL Server by downloading and installing the Microsoft Data Access Components (MDAC) 2.5 and 2.7 packages on the Windows computer running IIS. You can download the MDAC packages for free from the Microsoft website at http://msdn.microsoft.com/data/mdac/downloads/.

check this article and follow the steps and verify the things are correct in your scenario:

http://www.eggheadcafe.com/forumarchives/SQLServersetup/Oct2005/post24034762.asp

TRYTHIS LINK

C_A P replied to Ramya M. on 17-Dec-08 04:02 AM
maybe this can help  you
http://www.issociate.de/board/post/212908/ODBC_connection_to_SQL_server_failing.html
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q328306
READ THIS
C_A P replied to Ramya M. on 17-Dec-08 04:07 AM

CAUSE

The most common reason that this connection attempt failed is that this DSN or...

The most common reason that this connection attempt failed is that this DSN or ODBC data source attempted to make a connection using the TCP/IP sockets Net-Library, which is Dbmssocn.dll. Because the SQL Server is not listening for incoming connections for TCP/IP sockets clients, the connection fails. From ISQL/w, the second error message stated above would be returned. The OS Error is 10061, and the function call that failed was ConnectionOpen(connect()).

WORKAROUND

By default, SQL Server will listen for incoming connections made by Named Pipes...

By default, SQL Server will listen for incoming connections made by Named Pipes clients. Named Pipes is the default IPC mechanism for clients to connect to a SQL Server version 4.2, 6.0, and 6.5 server. The Named Pipes DLL is Dbnmpntw.dll, and it should be located in the Windows\System or Winnt\System32 directory. The TCP/IP sockets netlib DLL is Dbmssocn.dll, and also should be in the Windows\System or Winnt\System32 directory.The most common resolution to this problem is to configure the client computer to connect using Named Pipes.

Configuring Named Pipes

If the Microsoft Data Access Components (MDAC) ODBC drivers have been installed on the computer, you can make this change in the second step of the Create New Data Source wizard.

NOTE: You can download MDAC drivers from:

http://msdn2.microsoft.com/en-us/data/aa937730.aspx (http://msdn2.microsoft.com/en-us/data/aa937730.aspx)

To configure the client, start the Create New Data Source wizard, click the Client Configuration button, and perform the following steps, based on the version of the SQL Server ODBC driver you are using:

For SQL Server ODBC Driver version 3.50

  1. Click the Net Library tab and use the drop down list box to set the default network (Net Library) to Named Pipes.
  2. On the Advanced tab, remove any advanced entries that reference the server you are connecting to.
  3. Click Done.

For SQL Server ODBC Driver version 3.70

  1. In the Network Libraries section of the Edit Network Library Configuration dialog box, select Named Pipes.
  2. Click OK.

If you do not have the MDAC ODBC drivers installed, you can use the SQL Server Client Configuration Utility tool to set the default Net-Library to Named Pipes. You can install the client utilities from the SQL Server CD from the i386 directory.

Configuring TCP/IP Sockets

Server configuration:

In order for a client to connect to the SQL Server over TCP/IP sockets, the server needs to be listening on the server side sockets library. To add TCP/IP sockets support run SQL Setup.

  1. Press Continue.
  2. Press Continue again.
  3. Select Change Network Support
  4. Select TCP/IP Sockets
  5. Press Continue to accept the default Pipe Name and TCP/IP socket port 1433.
  6. Stop and restart the MSSQLServer service.

In order for you to confirm that your SQL Server is listening for incoming sockets connections, the following line should be in the current SQL Server error log found in \mssql\log. 

   ods      Using 'SSMSSO60.DLL' version '6.5.0.0' to listen on '1433'.
                              

Client configuration:

To configure the client, start the Create New Data Source wizard, click Client Configuration, and perform the following steps, based on the version of the SQL Server ODBC driver you are using:

For SQL Server ODBC Driver version 3.50:

  1. Click the Net Library tab and use the drop down list box to set the default network (Net Library) to TCP/IP Sockets.

OR

  1. Leave the default Net Library set to Named Pipes and on the Advanced tab, add a new entry where: 
2.              Server := SQL Server Name
3.              Network:= TCP/IP Sockets
4.              Connection String: = IP Address,1433  
5.              (There are no spaces between the IP Address, the comma, and the port 1433)
                                       
  1. Click Add/Modify.
  2. Click Done.

For SQL Server ODBC Driver version 3.70:

  1. In the Network Libraries section of the Edit Network Library Configuration dialog box, select TCP/IP.
  2. Enter the name of the SQL Server into the Server alias textbox
  3. Change the Computer name to the actual IP address of the SQL Server.
  4. Type 1433 in the port number textbox
  5. Click OK.

 

sql server error:11001
mangesh shete replied to Ramya M. on 17-Feb-09 04:26 AM
connection failed:
SQL State:'01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection open(Connect( )).
Connection failed:
SQL State:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.