I'm using a newly acquired SQL Server 2005 Developer Edition. Installed with WinXP authentication, so VS2005 wizards connect successfully with "Integrated Security=True" (i.e., without name and password elements).
However am unsuccessful making Ch8, DB Migration Util work with connection string for SQL Server as listed on pg 161, step 13. Get error that ...user "gene" is not associated with Trusted SQL Connection.
Created account as follows (from old SQL Svr 7 book):
---------------------------------------------------
sp_addlogin 'gene','xyz12345','ProjectTimeTracker'
go
USE ProjectTimeTracker
go
sp_grantdbaccess 'gene','gene'
------------------------------
Above was reported successful in SQL Mgr query script, but still fails programatically with same error message: ...not a trusted SQL connection. The account also fails to to connect in SQL Svr Mgmt Studio, using SQL Server Authentication.

Also tried 'Windows Authentication' connection string:
"Provider=SQLOLEDB;Data Source=localhost;Integrated Security=TRUE;Initial Catalog=ProjectTimeTracker;"
but still get (different) error result.

Help! Thanx!

ps. I have an Admin Guide to SQL Server 2005 book on order.

In the SQL Server Configuration Manager, ensure that Named Pipes and TCP/IP protocols are enabled for your SQL Server. You may have to restart the service once you enable these protocols. Once you do that, you should be able to connect using integrated security or using a user and password.

Thearon

Thanx for your response, Thearon. Tried as you suggested but it still didn't work.

I had installed SQL Server 2005 with Windows Authentication only, so I reinstalled with both Windows and SQL Authentication, created a SQL-Server-only account, and am now able to connect programmatically via SQL Authentication, as expected. For the moment I will ignore Windows Authentication connection as I work thru the rest of the book - maybe later when I get smarter.

What puzzles me is that the DB wizards in Visual Studio 2005 got me connected with Windows Authentication in the earlier exercises, but I can't seem to connect via VB code. Can't get the right syntax for the connection string??

The book should have the correct syntax for a connection string using SQL Server Authentication. If you are having trouble with that connection string then please review this article to ensure your SQL Server Authentication is setup properly. http://msdn2.microsoft.com/en-us/library/ms143705.aspx#

Thearon

As stated, I am having no problem with SQL Server Authentication, only with Windows Authentication connection string, which is the authentication mode I would prefer to use. As stated in the article you cite:

"When a user connects through a Microsoft Windows user account, SQL Server validates the account name and password using information in the Windows operating system. This is the default authentication mode, and is much more secure than Mixed Mode. Windows Authentication uses Kerberos security protocol, provides password policy enforcement in terms of complexity validation for strong passwords, provides support for account lockout, and supports password expiration.
Security Note:
When possible, use Windows Authentication."

Thanks for your continued support, Thearon.

Gene

Have you tried searching google for the error you received? I typically find this most helpful as someone else always seems to have the same problem.

Thearon

Thearon,

I am having exactly the same issue that is stated in this posting. Has anyone figured out what the issue is. I am using Window Authentication and getting the error that the "Login failed for this user "tom", this user is not associated with at trusted SQL Server connection.

Any help would be appreciated

Thanks

Check the other posts for this book as there are similiar issues and resolutions.

Thearon

If SQL 2005 was installed using Windows Authentication then "Mixed Mode" Authentication is automatically disabled for all user accounts including "sa". You need to enable the correct authentication mode in the SQL Server Management Studio. Type "not a trusted SQL Connection" into Google for a number of articles on how to do this.

Alternatively you could enhance the WDABase class overloaded "New..." constructor, to accept a boolean variable indicating whether or not you wish to use Integrated Security for your SQL connection. You could then set this variable using a checkbox control on the main form. (I am not familiar with Oracle databases and therefore am unclear whether or not this approach is appropriate for Oracle databases)

The code might look something like this:

Public Sub New(ByVal Provider As String, ByVal Server As String, _
                    ByVal Database As String, ByVal Login As String, ByVal Password As String, _
                    ByVal useWindowsAuthentication As Boolean)
        If Provider = "SQL Server" Then
            If Not useWindowsAuthentication Then
                Connection = New OleDbConnection( _
                    "Provider=SQLOLEDB;" & _
                    "Data Source=" & Server & ";" & _
                    "Database=" & Database & ";" & _
                    "User ID=" & Login & ";" & _
                    "Password=" & Password & ";")
            Else
                Connection = New OleDbConnection( _
                    "Provider=SQLOLEDB;" & _
                    "Data Source=" & Server & ";" & _
                    "Database=" & Database & ";" & _
                    "Integrated Security=SSPI" & ";")
            End If
       Else
            Connection = New OleDbConnection( _
                "Provider=MSDAORA;" & _
                "Data Source=" & Server & ";" & _
                "User ID=" & Login & ";" & _
                "Password=" & Password & ";")
        End If
    End Sub

All you would then need to do is change the btnOpenConnection_Click event on Form1 as follows:

Private Sub btnOpenConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenConnection.Click
        If chbWindowsAuth.Checked Then
            objData = New WDABase(strProvider, txtServer.Text, txtDatabase.Text, String.Empty, String.Empty, True)
        Else
            objData = New WDABase(strProvider, txtServer.Text, txtDatabase.Text, txtLoginName.Text, txtPassword.Text, False)
        End If
        Try
            objData.OpenConnection()
            lblStatus.Text = "Database opened"
        Catch ExceptionErr As Exception
            MessageBox.Show(ExceptionErr.Message)
        End Try
    End Sub

You can then add some tidy up code to the optOracle_CheckChanged and optSQLServer_CheckChanged events to make the user interface work correctly.