help needed establishing connection with sql-server

[demoti]

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:

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

[mendhak]

Do you have to use a system DSN?

[demoti]

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!

[mendhak]

First, Import System.Data.SqlClient

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

Where connectionstring would be:

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.

[mendhak]

To retrieve the data in a dataset,

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

[demoti]

I'm trying it immediatly!

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

[demoti]

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 :System.Data.SqlClient.SqlException: Login failed for user 'artscar'. Reason: Not associated with a trusted SQL Server connection.

[mendhak]

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

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

[demoti]

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?

[mendhak]

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

[demoti]

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

[demoti]

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?

[demoti]

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