Sql Server Express 2008 R2 Error on XP but not on Windows 7
-
I have an application that I am converting to SQL Server Express 2008 R2. I have the application working just fine in Windows 7 but the application does not work on XP. Here is the error I get in the error log:
2013-12-26 15:13:47.34 spid51 Starting up database 'Reference'.
2013-12-26 15:13:48.56 spid52 Starting up database 'Data'.
2013-12-27 01:00:18.15 spid1s Server resumed execution after being idle 34218 seconds. Reason: timer event.
2013-12-27 07:51:59.21 spid51 Starting up database 'Reference'.
2013-12-27 07:52:03.29 spid52 Starting up database 'Data'.
2013-12-27 07:52:03.54 spid52 Error: 9003, Severity: 20, State: 15.
2013-12-27 07:52:03.54 spid52 The log scan number (47:428:1) passed to log scan in database 'Data' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2013-12-27 07:52:03.82 spid52 Error: 3414, Severity: 21, State: 1.
2013-12-27 07:52:03.82 spid52 An error occurred during recovery, preventing the database 'Data' (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2013-12-27 07:52:03.93 Logon Error: 18456, Severity: 14, State: 38.
2013-12-27 07:52:03.93 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-12-27 10:11:56.34 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
I don't understand why the exact same installation process, connection string, and code do not work the same way between the 2 operating systems. I have noticed that if I use the Management Studio it shows the databases as read-only on the xp system, which it does not do on the windows 7 system.
Does anyone have any ideas why it would not behave the same?
-
Did you verify that all files (data and log files) are succesfully copied to the very same folder (and drive!)?
It seems like the log file either does not match the backup file or a log file is missing (leading to a broken log chain).
Can you please describe your installation process, especially the part related to restore the database?
-
First off, I have started doing some testing and have found that if I don't use the user "sa" and password in the connection string on XP it allows my application to access the databases. This is what is puzzling me - why would it work on Windows 7 but not XP?
As far as the installation goes, I am using the bootstrapper that was published on the web. Within that I specify the password for the sa account. I attach to the databases that are copied as part of my application installation and do not use backup and restore - I just let the application attach to it. I do not do a restore because the user can uninstall and reinstall but the databases must retain their data so we do not want to restore from old data.
As I have said, this works just fine in Windows 7. If nobody has a good reason why the connection string with sa does not work on xp, I may just put in a check for the OS and if it is XP just use the non sa string.
-
jdixon-586320 (12/30/2013)
First off, I have started doing some testing and have found that if I don't use the user "sa" and password in the connection string on XP it allows my application to access the databases. This is what is puzzling me - why would it work on Windows 7 but not XP?As far as the installation goes, I am using the bootstrapper that was published on the web. Within that I specify the password for the sa account. I attach to the databases that are copied as part of my application installation and do not use backup and restore - I just let the application attach to it. I do not do a restore because the user can uninstall and reinstall but the databases must retain their data so we do not want to restore from old data.
As I have said, this works just fine in Windows 7. If nobody has a good reason why the connection string with sa does not work on xp, I may just put in a check for the OS and if it is XP just use the non sa string.
I don't think the issue is XP vs Win7; i'd bet it's because the SQL server is set for Windows Authentication only, and not mixed mode; in that situation, using sa wouldn't work, right? can you change your app to try a trusted connection instead? or try-catch to use 'sa' an then trusted, and finally fail?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
If I am reading the bootstrap statement correctly, this should be mixed mode:
Arguments='/q /hideconsole /action=Install /features=SQL /instancename=SQLEXPRESS
/enableranu=1 /sqlsvcaccount="NT Authority\NetworkService"
/AddCurrentUserAsSqlAdmin /skiprules=RebootRequiredCheck
/IAcceptSQLServerLicenseTerms /SECURITYMODE="SQL" /SAPWD="xxxx"'
-
I tried to change the connection string for xp to have no user login, but it then fails if another user (even with admin permissions) tries to run the application (which accesses the databases).
So I am back to my basic question again - why is this acting differently for logging in as sa in XP????
-
After much searching I found the solution. When I install my application I need to give the system user "Network Service" permissions to the data directory where the databases are kept (since they are not in the default location). After adding this everything works fine in both XP and Windows 7.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply