Connect to SQLServer using Windows Authentication

[mbaker]

Hi,

How do I connect to a SQL Server Database using Windows Authentication. I am using VB.Net and SQLServer. My project is an ASP Web Application.

I am trying to use the following connection string:

Public xConnection As New OleDb.OleDbConnection("Provider=sqloledb;server=myserver;Trusted_Connection=yes;Integrated Security=sspi;database=mydbase")
xConnection.Open

But I get the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I have tried other connection strings but i either get the above error or Login failed for user ASPNET????? The user ASPNET has permissions to my database.

I can get into my database using Windows Authentication through Query Analyzer

If i put in a valid username and password it connects but i would rather use windows authentication.

Thanks for any help.
Michelle

[cim3]

Don't think you can do that with oledb object. Use the SqlClient object to create a connection.

[mbaker]

thanks,

You don't happen to have an example of how to do this, or a web site with examples? I have had a look around but all the examples i have seen have user name and passwords rather than use windows authentication.

thanks
Michelle

[Zandramus]

here is a function for building your connect string in vb.net
' in this connection string a70tgsql1 is my sql server
' I use windows authentication with the intigrated command
' and I set the initial database to the northwind database

' I got this from a tutorial on microsofts site and modified it
' to use windows authentication.
' note your sqlserver will have to be configured to accept
' windows authentication

Private Function ConnectStringBuild() As String
Dim strConn As String
strConn = "Data Source=a70tgcsql1;Integrated Security=SSPI;Initial Catalog=northwind"
Return strConn
End Function

' then here is a function where I call it and load a listbox

Private Sub ListLoad()
Dim oCmd As SqlClient.SqlCommand
Dim oDR As SqlClient.SqlDataReader
Dim oItem As PDSAListItemNumeric
Dim strSQL As String
Dim strConn As String

strConn = ConnectStringBuild()

strSQL = "SELECT ProductID, ProductName "
strSQL &= "FROM Products"

Try
oCmd = New SqlClient.SqlCommand()
With oCmd
.Connection = New SqlClient.SqlConnection(strConn)
.Connection.Open()
.CommandText = strSQL
oDR = .ExecuteReader()
End With

lstProducts.Items.Clear()
Do While oDR.Read()
oItem = New PDSAListItemNumeric()
With oDR
oItem.ID = CInt(.Item("ProductID"))
oItem.Value = .Item("ProductName").ToString()
End With

lstProducts.Items.Add(oItem)
Loop
If lstProducts.Items.Count > 0 Then
lstProducts.SetSelected(0, True)
End If

Catch oExcept As Exception
MessageBox.Show(oExcept.Message)

End Try
End Sub

[mbaker]

thank-you i'll give it a go today