SQLState: '42000' SQL Server Error: 4060 using MSDE on XP

getting error:

" Connection failed:
SQLState: '42000'
SQL Server Error: 4060
Cannot open database requested in login 'Recurrent'. Login Fails "

Am running a small VB 6.0 application on XP using MSDE and continue to get the above error message. I can log into the server with the command "  osql -U sa  " then enter password and allowed access, but when doing this through the VB application the error message persists. Can anyone suggest a sequence of steps for resolution? Key items to look for? Reference material/websites? Thank you.  
Who is Participating?
SjoerdVerweijConnect With a Mentor Commented:
The connect string you provided is a SQL login (as opposed to Windows Auth). Your server needs to be in mixed mode to support this. To ensure this, look on the Security tab in the server's properties dialog (right-click on it in Enterprise Manager, select Properties).

Have you tried disabling the Windows Firewall to see if that helps? (Control Panel -> Security Center).
Did you try to login as user Recurrent using osql -U Recurrent?
What is result?
- Open up Enterprise Manager
- Go to your server, expand it
- Expand Security
- Expand Logins
- Find Recurrent, right-click, properties
- Under the Defaults section, look at the Database drop-down.

Odds are that Recurrent has no access to the database selected there. You can do one of two things:

- Change the drop-down to a database the login does have access to
- Grant Recurrent access to that database (go to the Database Access tab and check the checkmark in front of the database that was selected in the dropdown).

For obvious reasons, the first alternative is preferable.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

jedannerAuthor Commented:
patrikt. I ran a small batch file to slow down the message in order see the output:

osql -U Recurrent

The message returned,  "Login failed for user 'Recurrent'"
SjoerdVerweij. This MSDE version is scaled down, am not finding any Enterprise Manager. Are there ways to run this from the command line or do you have an example how to hard code it into the VB 6.0 application?  

Thank you both.

jedannerAuthor Commented:
What's strange is that I can log into my local server as "sa" but patrikt's comments are making me think of Recurrent as an additional user that can not log in, so perhaps with a password conflict. Do these error messages support this thinking? (I don't know what Recurrent is).  

SQLState: '42000' = Syntax error or access violation

SQL Server Error: 4060 = Ensure that the user ID, password, and Initial Catalog to the OLEDBConnectionString parameter of your RDA Pull method are correct.
Log in as sa and run these
EXEC sp_helplogins
USE <your database name>
EXEC sp_helpuser

You will see logins and default databases in first and users in your database in second.

jedanner: Recurrent is a user, yes. Usually, you get this error because the login has a default database (database you end up in if you don't specify one) that the user does not have access to. Run sp_helplogins to see which one that is. To change it:

sp_defaultdb 'Recurrent', 'databasename'
"This MSDE version is scaled down, am not finding any Enterprise Manager"   MSDE doesnt come with Enterprise Manager.
jedannerAuthor Commented:
hummmm SjoerdVerweij 'Recurrent' is a user?  ok.

When I check my database folder I have these files listed:

HS Recurrent_log.LDF

Inside the result.txt states:
1> 2> Device activation error. The physical file name 'C:\HSI
\database\new\Recurrent_Log.LDF' may be incorrect.
New log file 'C:\Program Files\CD
\database\HSRecurrent_log.LDF' was created.


When I log into MSDE and use command:  sp_helplogins
the following prints:

      DefDBName      DefLangName      AUser

BUILTIN\Administrators and a hex value
      master            us_english      NO
      master            NULL            yes

(2 rows affected)
LoginName DBNAME      UserName             UserOrAlias
-------- ----------    ----------              --------------
sa      master            db_owner            MemberOf
sa      master            dbo            User
sa      model                      db_owner            MemberOf
sa       model            dbo            User
sa      msdb            db_owner            MemberOf
sa      msdb            dbo            User
sa      tempdb            db_owner            MemberOf
sa      tempdb            dbo            User
(8 rows affected)

When I use command: sp_defaultdb 'Recurrent', 'Recurrent_Data.MDF'
I get error message, 'Recurrent_Data.MDF' does not exist. Use sp_helpdb to
show available databases.

Then I use command and will post output shortly.

Thanks guys.  
jedannerAuthor Commented:
sp_helpdb produces following output:



Status=ONLINE, Updatedability=..., UserAccess=MULTI_USER, Recovery=SIMPLE, Version..., Collation...,SQLSortOrder..., etc etc

__ MB
Status, Updatedability..etc same

__ MB


jedannerAuthor Commented:
What's so odd is that I was informed all was working weeks ago. Would updating XP with the SP2 affect MSDE and require the SP3 server update?
It shouldn't. What is the connection string you're using in your VB6 app?
(I think you might be passing the database name as the login name)
Yes Sjored is right.
I'll sort out results you gived us:

You have one database (others are system) named HSRecurrent.

Members of BUILTIN\Administrators group can login to SQL with Windows authentication
and user sa can login with SQL authentication. No other users can login.
Default database is set to master for all.

Results it gives to me are:
There is no user named Recurent. May be was deleted or you have error in connection string.
You are confused by database files on disk. Don't look on disk, use logical database names; it is HSRecurrent is your database.
You have to set database in connection string to HS recurrent becouse default is master and it is wrong.

Show us connections string. Check connection properties in your application.

jedannerAuthor Commented:

Thank you all.

Patrikt, The application will not open until passwords and database authenitcation is complete/verified. A shut down sequence immeadiately follows. Since the connection to the database is in question and doesn't permit forward steps, I'm not able to check properties from the application. This however is the VB that I believe would be executed:

Private Sub cmdOK_Click()

    strUserName = Trim(Me.txtLogIn.Text)
    strPassword = Trim(Me.txtPassword.Text)
    strServerName = Trim(Me.txtServer.Text)
    frmInstall.sLogIn = "-U " & strUserName & " -P " & strPassword & " "
    sConnect = "Provider=SQLOLEDB.1;" _
                & "Password=" & strPassword & ";" _
                & "Persist Security Info=true;" _
                & "User ID=" & strUserName & ";" _
                & "Initial Catalog=" & Trim(txtDatabase) & ";" _
                & "Data Source=" & Trim(txtServer) & ";" _
                & "Use Procedure for Prepare=1;" _
                & "Auto Translate=True;" _
                & "Packet Size=4096;" _
                & "Workstation ID=" & Trim(Me.txtWorkstation.Text) & ";" _
                & "Use Encryption for Data=False;" _
                & "Tag with column collation when possible=False"

    SaveSetting "DigitalImaging", "Startup", "ConnectString", sConnect
    Unload Me
End Sub

Regarding showing the connection string, does this code supply what information needed to prove Sjored correct ? If not what specifically can I look for in the code? Thank you.
Just set a breakpoint on the SaveSetting line, go to the immediate window, type ?sconnect and paste the results here (you might want to change/remove the password, of course :-) )
jedannerAuthor Commented:
Sjoerd: I tried the immediate window and typed '? sConnect' (after setting breakpoint on SaveSetting line) and the application starts, but doesn't get past the logon. The application closes before any printout inside the VB studio immediate window.

I may be doing it wrong; however I have accessed information from a log file and it includes this information and am hopeful it will suffice:

Connection string - Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=(local);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False
Database failed to open

O, the o of it all.

jedannerAuthor Commented:
More Points.

Try simply

Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=ACTUALMACHINENAME;
jedannerAuthor Commented:
Thank you SjorerdVerweij
Did that work?
jedannerAuthor Commented:
Not yet. I'm so new at this that I don't know where to change it. The info about the SQLOLEDB.1 came from the log file and changing the code, I'm not usre exactly where to do that or if it is done using Visual's Data Manager, etc. I'm just so thankful you are pointing me in right direction.
Just replace the entire sConnect = blurb with

sConnect = "Provider=SQLOLEDB.1;Password=OMITTED;Persist Security Info=true;User ID=sa;Initial Catalog=Recurrent;Data Source=ACTUALMACHINENAME;"

(on one line, after replacing OMITTED and ACTUALMACHINENAME with correct values)
jedannerAuthor Commented:
Thanks SjoerdVerweij, I think that will work.

Is there a way I can isolate from this huge vb project just this code for testing?  The immediate window did not work properly.

I would like to write a separate vb6.0 small project for testing this code and confirm only the vb project's ability to fire this command and communicate successfully with the current databases. Could you suggest reference material for a small test?

Thank you.
jedannerAuthor Commented:
I was just informed that this VB6.0 project initially worked on an XP machine using Office 2000 (unfortunately I have no access to the original machine). I'm now debugging this on a different XP machine and have Office 2002 running. When I uninstall the project, reboot, and access the project through Visual Studio many *.ocx files cannot be registered. Once I install VB6.0 Service Pack 6, the *.ocx files (mscomcl.ocx, comdlg32.ocx, msdatgrd.ocx, msinet.ocx) registering problem is solved. Then upon reinstallation of VB project I got an MSADO25.tlb error that required changing the Setup.lst file. Inside the Setup.lst file the msado25.tlb file was found and I changed (DLLSelfRegister) to (TLBRegister), solving the registering error of the msado25.tlb upon reinstallation.

I don't know, but I'm thinking that there's something the Office 2000 and Office 2002  that may affect this original problem of logging on to the local msde 2000 via the connection string. I am blind, but could the problem exist because of different versions of the Microsoft Data Access Components ?  

I don't know what MDAC is or what versions are currently being used, but it's a stab in the dark - VB project / MDAC affected by different Office 2000-2002 versions?  
jedannerAuthor Commented:
more points. needing H E L P in a big way.
jedannerAuthor Commented:
500 smackers
jedannerAuthor Commented:
Having updated XP with SP2 would that have changed the MDAC  and cause errors above on logon attempt?
Try installing the latest version of MDAC from
jedannerAuthor Commented:
Thanks SjoerdVerweij, upon doing so I found material supporting idea of conflict between MSDE 2000 and XP SP2.


How does Windows XP SP2 affect MSDE:

Windows XP SP2 may affect the behavior of Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). If you’re connecting to a network by means of TCP/IP, named pipes, or remote procedure call (RPC), the Windows Firewall will block the ability of the database to connect to the network. If you are using MSDE 2000 with SQL Server 2000 SP3a, you should not have a problem because the network and SQL Browser technology are already turned off by default.

So then I try to upgrade to SP3a ...

I must choose a command per using Mixed Mode or Windows Authenication.
I think I'm using Mixed mode, is there a way to confirm? The app does require sa and password
After keying in Mixed mode instructions into the command line, I get some error that says I must do it from
the control panel? Any ideas? Jeez it's just installing a service pack but I'm having problems with it.

jedannerAuthor Commented:
Way to go SjoerdVerweij!!! mixed mode changed, problem solved. Thank you so much!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.