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

jay-areAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfreudenCommented:
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

0
jay-areAuthor Commented:
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.  
0
RiteshShahCommented:
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

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

jay-areAuthor Commented:
Product Version:  10.0.1600.22

Level:  RTM

Edition:  Standard Edition (64-bit)
0
RiteshShahCommented:
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
0
jay-areAuthor Commented:
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?
0
jay-areAuthor Commented:
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.
0
mfreudenCommented:
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?
0
jay-areAuthor Commented:
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?

0
mfreudenCommented:
Try and get the SQL serve switched to:  "SQL Server and Windows Authentication" mode.

This can be done by right-clicking the server name within the SQL management console, then select the security tab, and change the option from "windows authentication" to   "SQL Server and Windows Authentication".  This is how your other servers are probably configured anyway.

Once this is done and there is a valud SQL logon for you to use, you're 1st connection string at the begining of this thread should work fine.
0
jay-areAuthor Commented:
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.
0
jay-areAuthor Commented:
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?
0
RiteshShahCommented:
yes, that might a cause, try setting proper rights, you may try using "SA" user.
0
jay-areAuthor Commented:
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!
0
RiteshShahCommented:
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.
0
jay-areAuthor Commented:
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?
0
jay-areAuthor Commented:
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.

??

0
RiteshShahCommented:
ohhh, are you using SQL Server 2008 beta or old CTP?
0
jay-areAuthor Commented:
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.
0
jay-areAuthor Commented:
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.
0
RiteshShahCommented:
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???
0
RiteshShahCommented:
ohh ok. if that's RTM, don't worry.
0
RiteshShahCommented:
can you show me which connection string SQL Datasource generated?
0
jay-areAuthor Commented:
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.
0
jay-areAuthor Commented:
"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.
0
RiteshShahCommented:
can you try using following connection string?

"data source=DA-SQL03;initial catalog= Customers;password=password;persist security info=True;user id=user1;"

if above string gives you an error about user1 permission, go to your SSMS of sql server, create new login, user and use that ID/PASS in above connectionstring.


use master
CREATE LOGIN [TempUser1] WITH PASSWORD=N'password' , DEFAULT_DATABASE=[Customers], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
go

use Customers
CREATE User[TempUser1] FOR LOGIN [TempUser1]
go

your connection new string would be

"data source=DA-SQL03;initial catalog= Customers;password=password;persist security info=True;user id=TempUser1;"
0
jay-areAuthor Commented:
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.
0
jay-areAuthor Commented:
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...
0
jay-areAuthor Commented:
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.
0
jay-areAuthor Commented:
I opened a new question in hopes of getting more responses.

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_24448934.html

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.