• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Problem Getting A Connection In ADO .Net

I am trying to connect to an MSDE database using ADO .Net.

I am in a .vb file of a of an aspx project.

Here's the code I'm using:

Imports ADODB
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter

Dim objConn As SqlConnection
        objConn = New SqlConnection("data source=db_setup;initial catalog=db_setup;persist security info=False;user id=rwelsh;packet size=4096")

Public Sub Page_Load(ByVal Sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim objConn As SqlConnection
        objConn = New SqlConnection("data source=db_setup;initial catalog=db_setup;persist security info=False;user id=rwelsh;packet size=4096")

        Dim become As New SqlCommand()
        Dim JobsDA As SqlDataAdapter = New SqlDataAdapter()
        Dim JobsDS As DataSet = New DataSet()
        Dim objComm
        objComm = objConn.CreateCommand()
        objComm.CommandText = "SELECT * FROM tblMessaging"

        objConn.Open()

I keep getting an error: SQL Server does not exist or access is denied.

The database does exist, I can get into it though Access, WinSQL, etc.

I've also tried using the OleDB object, but I get the same error.

Also, I've also tried this code on a SQL Server 2000 database with the same error.

Has anyone had any luck with ADO .Net?  If you could provide any help I'd really appreciate it.

RichW
0
RichW
Asked:
RichW
  • 8
  • 7
1 Solution
 
RichWAuthor Commented:
Ooops, forget the first Dim objConn statement.  That was a typo.
0
 
RichWAuthor Commented:
If I create an Access db, and link the SQL Server tables to that, I can get a connection using:

Dim oOleDbConnection As OleDb.OleDbConnection
        Dim sConnString As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\billingProject\db_setupSQLServer.mdb;" & _
                 "User ID=Admin;" & _
                 "Password=;"
        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
        oOleDbConnection.Open()

So, I'm just wondering, what is wrong with ADO .Net and SQL Server????
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Éric MoreauSenior .Net ConsultantCommented:
the "data source" argument must be filled with the name of your server
0
 
RichWAuthor Commented:
I did, emoreau.  That's exactly where I got the connection string for the Access db.  I saw your link in another question.

Thanks, but I have tried all of them and still get the error.

I went into VB 6 and used ADO 2.5, and I can connect to the SQL Server db no problem.

I've read that a bunch of people are complaining to Microsoft about ADO .Net's problems with SQL Server.

RichW
0
 
RichWAuthor Commented:
You're right.  I did do it correctly.  I made another typo online.  Thanks though!
RichW
0
 
Éric MoreauSenior .Net ConsultantCommented:
Have you tried using "integrated security"?

I have no problem connecting to SQL 2000 using this connection string:
"Data Source=(local);Integrated Security=SSPI;Initial Catalog=Portfolio_FMC"

And I use it this way:

    Public Function ExecSQL(ByVal pstrSQL As String, _
                            Optional ByRef pintRA As Integer = 0) As Boolean
        Dim objCN As New SqlConnection(ConnectionString)
        Dim objCmd As New SqlCommand(pstrSQL, objCN)

        Try
            objCmd.Connection.Open()
            pintRA = objCmd.ExecuteNonQuery()
            objCmd.Connection.Close()
            ExecSQL = True
        Catch ex As Exception
            ExecSQL = False
        Finally
            objCmd.Connection.Close()
        End Try
    End Function
0
 
RichWAuthor Commented:
emoreau,

I used your function and I still get the same error:


SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:


Line 87:         Dim objCN As New SqlConnection(objConn.ConnectionString)
Line 88:         Dim objCmd As New SqlCommand(pstrSQL, objCN)
Line 89:         objCmd.Connection.Open()
Line 90:         Try
Line 91:             objCmd.Connection.Open()
 

Source File: c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb    Line: 89

Stack Trace:


[SqlException: SQL Server does not exist or access denied.]
   System.Data.SqlClient.SqlConnection.Open()
   BillingTest.WebForm1.ExecSQL(String pstrSQL, Int32& pintRA) in c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb:89
   BillingTest.WebForm1.Page_Load(Object Sender, EventArgs e) in c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb:53
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

 
I can't figure it out!  I can get to it from Access and other tools, so I know the db is there with the data.

Thanks again.
RichW
0
 
Éric MoreauSenior .Net ConsultantCommented:
Have you tried using "integrated security"?
0
 
Éric MoreauSenior .Net ConsultantCommented:
where have you set your password?
0
 
Éric MoreauSenior .Net ConsultantCommented:
db_setup is the name of your server?
0
 
RichWAuthor Commented:
Hi emoreau,

Yeah, db_setup is the database name.

I'm VERY familiar with ADO, and ADO .Net isn't too far of a stretch from it, so I'm starting to think it's a security issue.

I also tried connecting to a SQL Server 2000 db and I get the same exact error.

I'm going to try it on my system at home and see if I get the same results.

I have tried integrated security.  I've tried just about everything I can find out there, but I still get the same error.

I can get a connection on Access and MySQL, but for some reason I get the error with SQL Server.

RichW
0
 
Éric MoreauSenior .Net ConsultantCommented:
I asked: db_setup is the name of your server?
You answer: Yeah, db_setup is the database name

database name and server name are 2 different things!

Your connection string should look like this:
objConn = New SqlConnection("data source=YourServerName;initial catalog=db_setup;persist security
info=False;user id=rwelsh;packet size=4096")
0
 
RichWAuthor Commented:
I'm sorry, emoreau, I thought I said earlier that I had made a typo.  The datbase name is db_setup, the server name is richardwelsh\VSdotNET.  I realize I typed it wrong earlier, and was just responding that db_setup is the db name.

I'm on MAJOR sinus medications that are making me loopy.  lol

There were three separate questions when I came back into the thread, and the "yeah" was in response to a previous posting question of security, and I then answered in the same sentence the other question of the db name.

I assure you it's not something as simple as entering the wrong data source.

As I said, I can get a connection wtih Access and MySQL, but not SQL Server.  I've just loaded MSDE on my XP box at home, and I'm going to try it here.  I will let you know how I make out.

Thanks for hanging in there with this one.
RichW



0
 
RichWAuthor Commented:
emoreau, thanks for your help.  I figured out that I had to configure the ASP.NET worker process to run under the SYSTEM account in the <processModel> section of the Machine.config file.  

Because of security concerns, the ASP.NET worker process runs under the default ASPNET account. If you do not enable impersonation for an application, all of the threads that run the requests for that application run under the process account.

This problem occurs because the ASPNET account does not have sufficient permissions to connect to or write to an Access database.

For security reasons, Microsoft recommends that you enable impersonation on your ASP.NET application. This method works if the impersonated user has necessary permissions to the computer and the database that you are accessing.

Since you are the only one who responded and you hung in there with me, I'm giving you an A and I upped the points.

Thanks again!
RichW


0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now