connection to sql server 2005 problem

  • Thread starter Thread starter jcandamo
  • Start date Start date
J

jcandamo

I'm new to SQL server programming and trying to open a connection to
MS SQL Server 2005 from VB.NET. I created a new databse called lion;
thus in the browsing file folder window in "MS SQL Server Management
Studio Express" lion appears under "Databases"
and changed the "Databases>security>logins>sa" password to xxx

from vb.net I try to connect but get the following error:
"Login failed for user 'sa'. The user is not associated with a trusted
SQL Server connection."

If I right click on lion and go to properties the server name is DCORE
\SQLEXPRESS

I know is basic, but I'm having problems finding the solution to this
in the previous postings. Any help on this matter would be greatly
appreciated

The complete code is this:

Namespace lion
Public Class DB
'database connect
Private Function connect() As SqlConnection
Dim connectionstring As String
connectionstring = "Initial Catalog=lion;Data Source=DCORE
\SQLEXPRESS;User Id=sa;Password=xxx"
Dim cn As New SqlConnection(connectionstring)
Return cn
End Function

Public Function login(ByVal un As String, ByVal psw As String)
As DataSet
Dim cn As New SqlConnection
Dim sql As String
Dim ds As New DataSet
Dim dtContacts As New DataTable("Customers")
ds = Nothing
sql = "SELECT * FROM Customers'"
'use the connection function created above
cn = connect()
cn.Open()
'can't load connection
If cn.State = 0 Then
Exit Function
End If
Dim da As New SqlDataAdapter(sql, cn)
da.Fill(ds, "Contacts")
'cn.Close() -- note: connection closed automatically by
da.Fill

Return ds
End Function
End Class
End Namespace
 
I'm new to SQL server programming and trying to open a connection to
MS SQL Server 2005 from VB.NET. I created a new databse called lion;
thus in the browsing file folder window in "MS SQL Server Management
Studio Express" lion appears under "Databases"
and changed the "Databases>security>logins>sa" password to xxx

from vb.net I try to connect but get the following error:
"Login failed for user 'sa'. The user is not associated with a trusted
SQL Server connection."

If I right click on lion and go to properties the server name is DCORE
\SQLEXPRESS

I know is basic, but I'm having problems finding the solution to this
in the previous postings. Any help on this matter would be greatly
appreciated

The complete code is this:

Namespace lion
Public Class DB
'database connect
Private Function connect() As SqlConnection
Dim connectionstring As String
connectionstring = "Initial Catalog=lion;Data Source=DCORE
\SQLEXPRESS;User Id=sa;Password=xxx"
Dim cn As New SqlConnection(connectionstring)
Return cn
End Function

Public Function login(ByVal un As String, ByVal psw As String)
As DataSet
Dim cn As New SqlConnection
Dim sql As String
Dim ds As New DataSet
Dim dtContacts As New DataTable("Customers")
ds = Nothing
sql = "SELECT * FROM Customers'"
'use the connection function created above
cn = connect()
cn.Open()
'can't load connection
If cn.State = 0 Then
Exit Function
End If
Dim da As New SqlDataAdapter(sql, cn)
da.Fill(ds, "Contacts")
'cn.Close() -- note: connection closed automatically by
da.Fill

Return ds
End Function
End Class
End Namespace
Click to expand...

Check your server authentication in SQL Server Management Studio under
server properties => security. It should be set to SQL Server and Windows
Authenication mode.

My guess is it is set to Windows Authentication.

Ken
 
You were right. I changed that setting. However, in the line pf code:

cn.Open()


Now I get the following error:

A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.)

Thanks for your time and help

The complete code is this:

Namespace lion
Public Class DB
'database connect
Private Function connect() As SqlConnection
Dim connectionstring As String
connectionstring = "Initial Catalog=lion;Data
Source=DCORE
\SQLEXPRESS;User Id=sa;Password=xxx"
Dim cn As New SqlConnection(connectionstring)
Return cn
End Function


Public Function login(ByVal un As String, ByVal psw As
String)
As DataSet
Dim cn As New SqlConnection
Dim sql As String
Dim ds As New DataSet
Dim dtContacts As New DataTable("Customers")
ds = Nothing
sql = "SELECT * FROM Customers'"
'use the connection function created above
cn = connect()
cn.Open()
'can't load connection
If cn.State = 0 Then
Exit Function
End If
Dim da As New SqlDataAdapter(sql, cn)
da.Fill(ds, "Contacts")
'cn.Close() -- note: connection closed automatically by
da.Fill


Return ds
End Function
End Class
End Namespace
 
Obviously you are 'running' your 'application' on the machine named DCORE,
i.e, the same machine where SQL Server Express is installed. The reference
to 'Shared Memory' indicates this.

If one is not careful when installing SQL Server Express, it can result in
one or more of the SQL Server related services being shut down when it is
not in use.

In addition, by default, a database created in SQL Server Express is set to
'auto close'. This means that when such a database has no connections it is
physically unloaded from the SQl Server 'environment'.

If any of the services need to be started or a databse needs to be loaded
then, at connect time there can be some latency.

One way to deal with this is to retry after a delay until a connection is
achieved.

For example:

Private Function connect() As SqlConnection

Dim _con = New SqlConnection("Initial Catalog=lion;Data
Source=DCORE\SQLEXPRESS;Integrated Security=SSPI")

Dim _tries = 0

Do
Try
_con.Open()
_con.Close()
Return cn
Catch
_tries += 1
If _tries = 3 Then Return Nothing
Thread.Sleep(5000)
Finally
_con.Close()
End Try
Loop

End Function

This illustrates retrying with a delay until either, a connection is made or
3 attempts have been made, whichever occurs first. If a connection was
made, the connection object is returned otherwise Nothing is returned, which
can be trapped by the caller.

Obviously, 'sleeping' for 5 seconds at a time will not be terribly
desirable. Rather, it is used here to illustrate a point.

Also, one would need to refine the 'Catch' so that one can deal with various
exceptions appropriately.

The caller will need tweaking as well, for example:

Public Function login() As DataSet

Dim _con = connect()

If _con Is Nothing Then Return Nothing

Dim _da = New SqlDataAdapter("SELECT * FROM Customers", _con)

Dim ds = New DataSet

_da.Fill(ds, "Contacts")

Return _ds

End Function

Note that this now returns Nothing if a connection was not made.

If connect() returns a connection object but login() itself fails then some
other exception occurred.
 
Thank you for your reponse,

is there a way to make sure all necessary services are running to open
a connection? or at least to run all services manually so I can get
this small code working? do you know which services could be
potentially closed?

Thanks again
 
Those questions would be better addressed in
microsoft.public.sqlserver.server.
 
Back
Top