Start Free Trial

asked on

How do I connect to SQL08 through asp.net?

Hello Experts:

I'm having trouble using one of my everyday .net programs to connect to our new sql08 server. I have a sql2k and a sql2k5 server that never give me grief with this program. Whenever I try to connect to one of the databases I get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I've tried it using my normal connectionstring that works in 2k and 2k5. I tried it with the IP of the sql server, then I tried using the named instance of the server or what I THINK is the named instance...

I've tried odd combos of "myserver\instance" and all the rest. I tried using the standard connectionstrings from: http://www.connectionstrings.com/sql-server-2008 as well. Still the same error. My router has a firewall rule to allow port 1432-1434 through to my server. The sql server is also our webserver so I'm sure there is some permissions issue that I'm not able to find. The program I'm testing is a vb.net web app. Here is the sub for db connect:

Sub Button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            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"
            'sql08 connectionstring from link above - Dim ConnectionString As String = "Server=DA-SQL03\SQL03;Database=customers;User ID=user1;Password=password;Trusted_Connection=False;"
            Dim conn As New SqlConnection(ConnectionString)
            Dim query As String = "SELECT * FROM login WHERE username = @username AND password = @password AND clientid = @clientid"
            Dim newcommand As New SqlCommand(query, conn)
            newcommand.Connection = conn
            conn.Open()

Open in new window

What version of SQL 08 are you using?

If it's 2008 express, you need to allow for remote access:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

ASKER

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

Open in new window

ASKER

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

ASKER

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?

ASKER

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?

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

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.

ASKER

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.

ASKER

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

ASKER

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?

ASKER

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.

ASKER

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?

ASKER

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.

ASKER

"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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

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...

ASKER

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial