How do I connect to SQL08 through asp.net?

I don't believe I have express.  I don't see Express anywhere, also in the config manager I don't see a section for sql express.  TCP/IP is enabled though.  Windows firewall is off.  

can you please run following query and give us the exact result coming from it?

--To get the product version, use :
SELECT SERVERPROPERTY('productversion') as ProductVersion
 
 
--To get the Service Pack information or the level of version, use :
SELECT SERVERPROPERTY ('productlevel') as Level
 
 
--To get the product edition, use :
SELECT SERVERPROPERTY('edition') as EDITION

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Product Version:  10.0.1600.22

Level:  RTM

Edition:  Standard Edition (64-bit)

well Jay-are, now it is confirmed that you are not using express but standard so there might be quite a few reason which can show you this error, my friend and SQL Server MVP has recently written one article which covers almost all possibility of this error. have a look.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Ritesh Shah
www.SQLHub.com

Ok I completed all the steps in that article.  I didn't have an alias to begin with but I added one anyway.  Not sure about that last step.

I rebooted after doing everything just to make certain.  I still get the same error.  I think that perhaps my connection string is just wrong.  This is the first sql server I've had that had a named instance...Typically I just connect to my other servers using the IP for the server name.  So I'm not certain I'm using the right string.  Here is the original:

Dim ConnectionString As String = "workstation id=""me"";packet size=4096;user id=user1;password=password;data source=""DA-SQL03\SQL03"";persist security info=False;initial catalog=customers"

and the one I tried from the site listed above:
Dim ConnectionString As String = "Server=DA-SQL03\SQL03;Database=customers;User ID=user1;Password=password;Trusted_Connection=False;"

The server name inside the console shows: DA-SQL03\SQL03

The user is setup is called user1.  I've tried different variations of the server name and none seem to get me past that error.  Any other ideas?

I really think it is a problem with the connection string I'm using.  I changed to this:

Dim ConnectionString As String = "Server=""DA-SQL03\Sql03"";Database=Customers;User ID=DA-SQL03\administrator;Password=password;Trusted_Connection=True;"

and now I get the following error:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Throws this exception when I try to open the connection in my vb.net program.

When you use Truseted Connection=True, the UserI d and Password are ignored and the system attempts to log you in as the Current ActiveDir user.  If this is a scheduled job, then it will use the ActiveDir user the job runs under,

Based on your errors, it sounds like the SQL server was installed in "Windows Authentication" mode only and NOT in "SQL Server and Windows Authentication" mode.  Therefore you'll have to use Trusted Connection=True.

3 qusetions:

1. Is the PC you're running the VB code in the same domain as the SQL server?

2. Can you access the SQL server with your activedir logon from SQL Server management console on your PC?

3. if so, are you running the VB code with same login?

Ah ok, I thought I was onto something with that Trusted Connection.

When I run the code from the actual server or my remote devbox I get the same error.  The server isn't part of a domain and isn't using Active Directory unless WS2k8 installs this by default, I certainly didn't choose it.  I assume my vb.net programs run using the asp.net user?

Yeah that was something I changed right away.  So it has been in sql/windows auth mode the whole time.  The other servers are setup this way which is why I'm so confused about this problem.

I changed my code back to my original connection string using the proper named instance.

When I use an incorrect username or pass I get an error right away when I try to open the connection.  When I use a good username/pass I get an error only when I try to fill a dataset with data from one of the databases.  That's when I get this named pipes error.  Does this mean its specifically a problem with the user setup on sql08?

yes, that might a cause, try setting proper rights, you may try using "SA" user.

I'm not sure if I'm setting the proper rights or not.  Sql08 already had an "sa" user setup.   When I try to check off the new databases for user SA in user mapping it gives me an error:  "Cannot use the special principal 'sa'."

I've tried lots of different user setups and they all produce the same Named Pipes error listed above...I'm sort of stuck here since I can't even get in the front door!

why do you want to try "check off" SA? you don't need it. why don't you try using SQL Datasource connection from design time and try to connect your database from that only. once you will do it, you can copy connection string from it.

I was trying to select the db I'm trying to access in user mapping.  The "customers" db isn't checked for user SA.  I don't think that matters though.  My other sysadmin "user1" has it checked and I still get the same error.

What do you mean by SQL Datasource connection?  Example?

sqldatasource example.

http://www.vbdotnetheaven.com/UploadFile/prvn_131971/SqlDataSourcecontrol12212006230045PM/SqlDataSourcecontrol.aspx

Interesting.  I added the datasource control to my webform and setup a connection string.  When tested in the wizard it works just fine, exactly like I have it on my code-behind.  However when I click "OK" to finish it says:  This server version is not supported.  You must have Microsoft SQL Server 2005 Beta 2 or later.

??

ohhh, are you using SQL Server 2008 beta or old CTP?

When I copy/paste the connectionstring it created I get this error:  Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'user1'.

Source Error:


Line 87:             Dim newcommand As New SqlCommand(query, conn)
Line 88:             newcommand.Connection = conn
Line 89:             conn.Open()
Line 90:             SqlDataAdapter1.SelectCommand.Parameters("@username").Value = TextBox1.Text
Line 91:             SqlDataAdapter1.SelectCommand.Parameters("@password").Value = TextBox3.Text
 

Source File: C:\inetpub\wwwroot\login\WebForm1.aspx.vb    Line: 89

Almost like I have the wrong credentials again but I don't.

Product Version:  10.0.1600.22

Level:  RTM

Edition:  Standard Edition (64-bit)

I'm pretty sure its the full blown sql 2008.  Doesn't say beta anywhere.

there was a bug in early SQL Server 2008, .NET framework was not able to connect it, I afraid you are out of luck if you are using it. why don't you try upgrading your SQL Server 2008???

ohh ok. if that's RTM, don't worry.

can you show me which connection string SQL Datasource generated?

Also, the connection string the wizard created didn't have the password field.  When I add that it gives me the same old Named Pipes error.

"Data Source=DA-SQL03;Initial Catalog=Customers;Persist Security Info=True;User ID=user1"
^^^ this is what it created

I added the password field to it and tested.

Ok the first string gave me the same named pipes error as before.  I created the new login anyway and used the second connection string and it still gives me the same error.

I've tried enabling/disabling the different protocols.  I updated the server to sql 2008 SP1.  I tried different web.config's for my application.  I'm not sure what the problem is.  I can connect to the server, it opens the connection.  I just can't fill a dataset without getting that named pipes error...

Still stuck on this.  I can open the connection to the server but I cannot fill a dataset with data from the server.  Ideas?  I'll try just about anything at this point.