Solved

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

Posted on 2004-09-08
32
13,116 Views
Last Modified: 2011-08-18
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.  
0
Comment
Question by:jedanner
  • 17
  • 11
  • 3
  • +1
32 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 12011441
Did you try to login as user Recurrent using osql -U Recurrent?
What is result?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12011677
- 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.
0
 

Author Comment

by:jedanner
ID: 12011962
--------------------------------------------------------------
patrikt. I ran a small batch file to slow down the message in order see the output:

c:
cd\
osql -U Recurrent
pause

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.

0
 

Author Comment

by:jedanner
ID: 12012027
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.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 12013576
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.

Patrik
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12018348
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'
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12019115
"This MSDE version is scaled down, am not finding any Enterprise Manager"   MSDE doesnt come with Enterprise Manager.
0
 

Author Comment

by:jedanner
ID: 12020621
hummmm SjoerdVerweij 'Recurrent' is a user?  ok.

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

HS Recurrent_log.LDF
Recurrent_Data.MDF
Recurrent_Log.LDF
results.txt

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.  
0
 

Author Comment

by:jedanner
ID: 12020701
sp_helpdb produces following output:

name
      dbsize
owner
         dbid
created
status

HSRecurrent
__MB

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

Master
__ MB
Status, Updatedability..etc same

model
__ MB

msdb
tempdb

0
 

Author Comment

by:jedanner
ID: 12020803
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?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12021060
It shouldn't. What is the connection string you're using in your VB6 app?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12021074
(I think you might be passing the database name as the login name)
0
 
LVL 12

Expert Comment

by:patrikt
ID: 12031104
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.

Patrik
0
 

Author Comment

by:jedanner
ID: 12047176

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.
 
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12047477
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 :-) )
0
 

Author Comment

by:jedanner
ID: 12061080
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.

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:jedanner
ID: 12065448
More Points.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12066081

Try simply

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

Author Comment

by:jedanner
ID: 12068731
Thank you SjorerdVerweij
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12070549
Did that work?
0
 

Author Comment

by:jedanner
ID: 12086511
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.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12088936
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)
0
 

Author Comment

by:jedanner
ID: 12114762
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.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12124064
0
 

Author Comment

by:jedanner
ID: 12136028
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?  
0
 

Author Comment

by:jedanner
ID: 12136073
more points. needing H E L P in a big way.
0
 

Author Comment

by:jedanner
ID: 12136082
500 smackers
0
 

Author Comment

by:jedanner
ID: 12136320
Having updated XP with SP2 would that have changed the MDAC  and cause errors above on logon attempt?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12137015
Try installing the latest version of MDAC from
www.microsoft.com/data
0
 

Author Comment

by:jedanner
ID: 12147354
Thanks SjoerdVerweij, upon doing so I found material supporting idea of conflict between MSDE 2000 and XP SP2.


http://www.microsoft.com/sql/techinfo/administration/2000/security/winxpsp2faq.asp

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.

Whew.
0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 500 total points
ID: 12147834
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).
0
 

Author Comment

by:jedanner
ID: 12192279
Way to go SjoerdVerweij!!! mixed mode changed, problem solved. Thank you so much!!!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now