search

OpenCDSS / cdss-website-hydrobase

Instructions how to attach HydroBase locally
0 stars 0 forks source link

Feedback on HydroBase install on Windows 11 #5

Open smalers opened 2 years ago

smalers commented 2 years ago

Download the latest installer from the OpenCDSS HydroBase page. This is a zip file. Unzip to a folder and follow the installation documentation.

The documentation Markdown has numbers that repeat. Need to update formatting to fix, for example by indenting consistently and avoiding line breaks. Consequently the numbers shown below don't match. The documentation could also be more clear about when a numbered instruction is for the previous or following image, for example use colon or "as shown in the following image", press "Next", etc.

  1. The "Choose Directory For Extracted Files" dialog defaulted to a DropBox folder, which is bad so specify a folder in the normal Downloads. I specified C:\Downloads\HydroBase\Temp instead.
  2. Pick New New SQL Server stand-alone installation or add features to an existing installation.
  3. Accept the license.
  4. Do not check "Use Microsoft Update to check for updates".
  5. Install Rules - the output looks like the documentation.
  6. Feature Selection - use the defaults.
  7. Instance Configuration - set the named instance and instance ID to CDSS.
  8. Server Configuration - use the defaults.
  9. Database Engine Configuration - change to Mixed Mode for authentication, enter the password, the SQL Server administrators default to BLUE\steve (steve).
  10. Complete shows all green.
  11. No reboots and also closed the SQL Server Installation Center dialog.
  12. Turn on the SQL Browser service to automatic (could have done this in an earlier step?). Perhaps explain why this is needed? It seems to be necessary for TSTool to discover available servers.
  13. For the HydroBase Database Manager, maybe explicitly indicate the name of the folder for the installer is CDSSLocalHydroBase_Installer.
    1. Complains about needing .NET 3.5 (I am installing on Windows) HydroBase-DotNetWarning Press Yes to install .NET 3.5. It brings up a website that hs .NET 6 and it is unclear how to get 3.5. I searched and found this web page. See the install success. DotNet35InstallSuccess
  14. Attach database:
    1. It is a bit confusing about how earlier in the documentation said "We recommend downloading and extracting HydroBase versions in a directory similar to C:\HydroBase\Data to make managing versions of HydroBase easier." and then the HydroBase Dtaabase Manager uses C:\CDSS\Data. Maybe the former should be under Downloads and the latter under C:\CDSS\HydroBase?. I know it is hard to change longstanding conventions.
    2. I have existing database files under C:\CDSS that were copied from my old computer. When I select an mdf file, I get this error (stack trace is below): DBAttachError Since it is an UnauthorizedAccessException, I reran the HydroBase Database Manager as administrator and it did not show the error.
    3. I picked an mdf file as before. It warns about wanting to move to the default folder, even though that is the same folder. The tool should not show this dialog if the file is already in the final location. I get another error: UnableToOpenThePhysicalFileError The file definitely exists for the given path and I am running as Administrator so not sure what is the problem. I am going to create an issue for the State of Colorado team and see what they say. Worst case I can try installing using a zip file.
       See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.UnauthorizedAccessException: Attempted to perform an unauthorized operation.
   at System.Security.AccessControl.Win32.SetSecurityInfo(ResourceType type, String name, SafeHandle handle, SecurityInfos securityInformation, SecurityIdentifier owner, SecurityIdentifier group, GenericAcl sacl, GenericAcl dacl)
   at System.Security.AccessControl.NativeObjectSecurity.Persist(String name, SafeHandle handle, AccessControlSections includeSections, Object exceptionContext)
   at System.Security.AccessControl.NativeObjectSecurity.Persist(String name, AccessControlSections includeSections, Object exceptionContext)
   at System.Security.AccessControl.FileSystemSecurity.Persist(String fullPath)
   at HydroBaseMaintenanceUtility.frmMain.FilePermissionSettings(String stDataSourceFileName, String stLogSourceFileName)
   at HydroBaseMaintenanceUtility.frmMain.btnAttach_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9164 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v2.0.50727/mscorlib.dll
----------------------------------------
HydroBaseMaintenanceUtility
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Program%20Files%20(x86)/Division%20of%20Water%20Resources/Hydrobase%20Database%20Manager/HydroBaseMaintenanceUtility.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.ServiceProcess
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.ServiceProcess/2.0.0.0__b03f5f7f11d50a3a/System.ServiceProcess.dll
----------------------------------------
System.Management
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
smalers commented 2 years ago

I downloaded HydroBase_CO_20220928.zip and saved to my Downloads\HydroBase-Data folder. When I select this zip file using the HydroBase Database Manager (still running as administrator) I get the following:

image

I thought it could be attached from a zip file? I unzipped the file for the additional steps below.

I restarted the HydroBase Database Manager (not as administrator, since SQL Server should have me as database administrator, right?). Selecting the mdf goes further. Press "Yes" to allow the mdf to be copied to the default SQL Data Directory. Then I get the following (stack trace below).

image

Oops, did not realize that the recommendation to copy the file is a SQL Server message and CDSS does not recommend doing that. The documentation lacks a description of how to download databases from the FTP site and unzip the files into the correct C:\CDSS\Data folder.

I copied the .mdf and .ldf files from that I unzipped to C:\CDSS\Data and tried to attach again. I get the same "Access is denied" error that I saw at the end of the previous issue comment. Note that for this new Windows 11 machine, when I view my account information it says that I am Administrator. This was set automatically when I set up the machine. So... I don't think I need to run programs as Administrator. And, the SQL Server Express install set me as database administrator. This leads me to believe that the error message may be something else?

At this point I wonder if I should use the SQL Server tools directly to attach. I don't do that often and it would be good if CDSS provided a quick set of instructions on that, especially if moving away from the custom installer and manager tools.

What next?

See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.IO.DirectoryNotFoundException: Could not find a part of the path.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.File.Move(String sourceFileName, String destFileName)
   at HydroBaseMaintenanceUtility.frmMain.btnAttach_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9164 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v2.0.50727/mscorlib.dll
----------------------------------------
HydroBaseMaintenanceUtility
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Program%20Files%20(x86)/Division%20of%20Water%20Resources/Hydrobase%20Database%20Manager/HydroBaseMaintenanceUtility.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.ServiceProcess
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.ServiceProcess/2.0.0.0__b03f5f7f11d50a3a/System.ServiceProcess.dll
----------------------------------------
System.Management
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.Data
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Transactions
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.9157 (WinRelRS6.050727-9100)
    CodeBase: file:///C:/Windows/assembly/GAC_64/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
smalers commented 2 years ago

Doug Stenzel reminded me that the troubleshooting documentation mentioned the access denied error (my bad for not seeing that). However, when I try to look at file permissions I don't see the MSSQL$CDSS user listed, as shown below:

image

I ran the Windows "Services" tool and looked at the SQL Server (CDSS) processes, which shows the following:

image

I went back to the database files and right-clicked...Properties / Security. Then next to "To change permissions, click Edit" I pressed Edit, which shows the following.

image

Then I selected Add...

I entered NT Service\MSSQL$CDSS in the text area and pressed Check Names, which converted it to MSSQL$CDSS. Press OK. Then select MSSQL$CDSS in the Permissions for HydroBase_CO_20220330_log.ldf dialog, select Full Control and press OK and then OK again. Do this for both the ldf and mdf file for a database.

Then run the HydroBase Database Manager and attach, but had to do it as Administrator because an unexpected exception occurred.

Running TSTool shows the two databases that I attached. The TSTool time series browsing feature works OK. Querying time series, such as Stream - Streamflow works OK, for database 20220330. I thought I was getting errors with the 20220928 database reading the time series but it is working OK now.

So, have I solved the problem good enough to move on? I'm a bit concerned that I ran some things as Administrator when I did not need to. I was concerned that Windows 11 had changed security requirements.

macphersonbr commented 2 years ago

That is similar to my experience with setup yesterday. I will update the documentation to include some of these details. I also had to run certain things as administrator - it may be new Windows 11 requirements.

smalers commented 1 year ago

I had to attach HydroBase_CO_20230512 on my Windows 11 machine and ran into this same issue. Here is a summary:

  1. Unzipped the HydroBase files into C:\CDSS\DATA.
  2. Run File Explorer as admin and changed the permissions on the mdf and log files as described above.
  3. Run the HydroBase Database Manager as admin.
    1. Use Attach DB to select the C:\CDSS\DATA\HydroBase_CO_20230512.mdb file.
    2. For the dialog "We suggest moving... to the default SQL Data Directory...", trying Yes showed an error so use No. Is the SQL Data Directory this same folder or a different folder?

The attachment was successful. I was able to use with TSTool. Did the troubleshooting documentation get updated for this? I did not see.

smalers commented 1 year ago

Brian said the documentation has been updated but I see missing images:

image

macphersonbr commented 1 year ago

Thank you for catching! Changed file extension capitalization to fix.