help needed establishing connection with sql-server

i have an asp.net application running on "aspnetserver" (win2000 adv server) under the profile "artscar" which is a local administratoraccount on "aspnetserver".

My sql database i'd like to connect to is on "sqlserver".

I have a working system DSN on "aspnetserver" pointing to the correct database on "sqlserver"


i try to connect with:

Code:

---

dim con as odbcconnection
dim cmd as odbccommand
dim constring as string = "DSN=MYDSN"
con = new Microsoft.Data.Odbc.Odbcconnection(constring)
con.open
cmd = new odbccommand("select......................", con) <------error occurs here

---

i get error 28000 :Login failed for user '(null)'.Reason:Not Associated with a trusted SQL Server connection.

i tried adding iusr_aspnetserver to the users on "sqlserver" and in sqlserver itself. iis is set up for anonymous access + integrated windows identification...

The debugging crashes at con.open, and i'm really lost at what to look for

:blush:

Do you have to use a system DSN?

that's what i am using... but only because i couldn't connect through sqlconnection...

I doesn't really matter how i connect, i'm willing to try everything!

First, Import System.Data.SqlClient

VB Code:

Dim sqlConn As New SqlConnection(connectionString) 
sqlConn.Open()

Where connectionstring would be:

VB Code:

connectionString =  "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=some_login;Password=some_password;"

In Enterprise Manager, ensure that you're using SQL Server authentication and that some_login is created.

To retrieve the data in a dataset,

VB Code:

Dim strSQL As String = "SELECT * FROM tablename"
    Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, sqlConn)
 
mySqlDataAdapter.Fill(ds)

I'm trying it immediatly!

i was reviewing (http://www.connectionstrings.com/), i'm trying hard

Code:

---

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page
    Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnMessagebox As System.Web.UI.WebControls.Button

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'sqlconnection
        Dim CON As New SqlConnection()
        Dim CMD As SqlCommand
        Dim DTR As SqlDataReader

        'standard security
        CON.ConnectionString = "Data Source=ZON;Initial Catalog=ElecFys;User Id=artscar;Password=*********"
        CON.Open()


    End Sub

---

How can i ensure that im using sql server authentification? I tried doing it by >tools>SQL server configuration properties>security. If i click the radiobutton "SQL server and windows", hit ok and then check again: it is still at "windows only". Maybe because this server is allready in use to serve some existing vb-applications?

In the section users(in sqlserver on ZON) under ElecFys(dbname) i added the user "ZON\artscar".

the error :cry: :System.Data.SqlClient.SqlException: Login failed for user 'artscar'. Reason: Not associated with a trusted SQL Server connection.

From www.connectionstrings.com, try this string as well:

Quote:

---

"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False"

---

Also, when you switched to sql server authentication, did you restart sql server?

no, i couldn't do that, the database was in use by other users as well...

but now that everyone is one lunchbreak i can restart. Will this change anything for the existing visual basic applications?

Yes it will. If other apps use it, you can specify to use SQL Server authentication for that particular database.

how can i do this? i can't seem to do it for just one database...

Code:

---

Imports System.Data.SqlClient

Public Class WebForm1
    Inherits System.Web.UI.Page
    Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnMessagebox As System.Web.UI.WebControls.Button

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'sqlconnection
        Dim CON As New SqlConnection()
        Dim CMD As SqlCommand
        Dim DTR As SqlDataReader

        'standard security
        CON.ConnectionString = "Data Source=ZON;Initial Catalog=ElecFys;User Id=artscar;Password=*********"
        CON.Open()


    End Sub

---

nothing works...

while i am in visual studio.net i have no problem browsing the tables in the sqldatabase. can't i use the account visual studio uses?

(http://msdn.microsoft.com/library/de...orUserNull.asp) ?