Jump to content

MSSQL Error when creating the admin user on fresh install of PWS v8


jack

Recommended Posts

Hi,

 

I'm setting up a fresh copy of PasswordState v8 with a new database. When i get to the "Create Admin Account" part of the installation I get the following error when trying to create the new admin user:


1	2017-08-15 21:19:11.000	General Error	Error Code = The statement has been terminated.  The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value., StackTrace =    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)     at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()     at Passwordstate.Setup.Setup_AddFirstFormsUser(String strUserID, String strFirstName, String strSurname, String strEmailAddress, String strPassword)	Error

That error seems to point to a date mismatch maybe? So if it helps here is some more information about my environment:

 

The following is the "Culture" of my servers


PS F:\Backup> Get-Culture



LCID             Name             DisplayName

----             ----             -----------

1044             nb-NO            Norwegian, Bokmål (Norway)

Additionally I use the following MSSQL collation on MSSQL 2016 with a compatibility level of 130 (SQL Server 2016)


Danish_Norwegian_CI_AS

That collation worked fine with version 7.

 

I still have the older install which has a version of PasswordState 7 which was upgraded to Passwordstate 8 and i can create new users there without any problems.

 

So at the moment I'm stuck on creating the initial Administrator user on this fresh install.

 

Let me know if you need anymore information.

 

Link to comment
Share on other sites

Hello Jack,

 

Sorry you've run into this issue. This may be a bit challenging for us to figure out, as we do only support the English language - we're not smart enough to know more than one language :(

 

There have been changes in version 8 in terms of the .NET Provider we use for database connectivity, and this is so we can support just having TLS 1.2 protocol enabled - for server hardening. With this change, we have had some date format changes, as this new SQLNCLI11 provider does not allow micro seconds to be passed to the database.


So I would expect this is either failing on the DateCreated field in the UserAccounts table, or FormsPassReset if you are using the forms-based authentication version of Passwordstate. Can you please tell us if you are using AD authentication or forms, and I will then compare the two account creation functions and see if there are any differences?

Regards

Click Studios

Link to comment
Share on other sites

7 hours ago, support said:

Hello Jack,

 

Sorry you've run into this issue. This may be a bit challenging for us to figure out, as we do only support the English language - we're not smart enough to know more than one language :(

 

There have been changes in version 8 in terms of the .NET Provider we use for database connectivity, and this is so we can support just having TLS 1.2 protocol enabled - for server hardening. With this change, we have had some date format changes, as this new SQLNCLI11 provider does not allow micro seconds to be passed to the database.


So I would expect this is either failing on the DateCreated field in the UserAccounts table, or FormsPassReset if you are using the forms-based authentication version of Passwordstate. Can you please tell us if you are using AD authentication or forms, and I will then compare the two account creation functions and see if there are any differences?

Regards

Click Studios

I'm using Forms authentication.

 

What is interesting is the Debug information is written to the database with a timestamp?

 

The servers and the software installed on them are in English, so I'm not sure it's a language issue. The "Culture" is however Norwegian so we get the correct date and time format although it's the same as Australia apart from us using the 24 hour time format.

 

Just to test the SQL collation in case it was something with that. I created a new database with the "Latin1_General_CI_AS" database collation and started from scratch. I still get the same error.

 

*Edit*

What i find interesting is that my older instance of Password state which i upgraded to Version 8 from version 7 doesn't have this problem. I can create new users there?

 

Link to comment
Share on other sites

That's very odd. All of our SQL Servers are set to a collation of 'SQL_Latin1_General_CP1_CI_AS'. I wouldn't have thought that collation is that much different to the one you selected.

 

We'll set up a new SQL Server tomorrow with your collation, and forms based authentication, and see if we can reproduce the issue.

Thanks for working with us on this.

 

Regards

Click Studios

Link to comment
Share on other sites

I finally got past the screen and it seems the solution was  to install the Webserver (Server 2016 Core) with "English - United States" Region Settings

 

All our servers are installed with the following (It's in the image):

Language: English United States

Time and Currency Format: Norwegian

Keyboard: Norwgian

 

I did a manual install with the following

Language: English United States

Time and Currency Format: English United States

Keyboard: Norwgian

 

The SQL Collation is the default for our SQL Servers "Danish_Norwegian_CI_AS".

 

I was now able to complete the installation and all seems well. So it was the region settings on the webserver causing the problems. I don't see any major problems with having the Server for Passwordstate using the United States regions settings. But it's obviously not optimal. However it's nothing major and won't hamper our use of the product.

 

 

 

Link to comment
Share on other sites

Hi Jack,

 

We must be missing something, as we cannot seem to replicate this issue. I've tried:

  • Time and Currency Format: Both Norwegian options
  • And setting SQL Collation to "Danish_Norwegian_CI_AS"

Rebooting the server after making these changes as well, and I cannot seem to fault this. Was your web server originally installed with English United States as the language, or was this changed afterwards? I also testing with SQL being installed on the same web server - I doubt that would make any difference.

Regards

Click Studios

Link to comment
Share on other sites

3 hours ago, support said:

Hi Jack,

 

We must be missing something, as we cannot seem to replicate this issue. I've tried:

  • Time and Currency Format: Both Norwegian options
  • And setting SQL Collation to "Danish_Norwegian_CI_AS"

Rebooting the server after making these changes as well, and I cannot seem to fault this. Was your web server originally installed with English United States as the language, or was this changed afterwards? I also testing with SQL being installed on the same web server - I doubt that would make any difference.

Regards

Click Studios

Yes, all the servers are installed in English - Trust me, no one uses language packs on a Windows Server. One of the main reasons is that error messages in Norwegian don't get many hits on Google :)

 

No culture/language or UI settings are changed after install. All we do is update the server, run our DSC script to install IIS and required modules. Then we install Passwordstate and off we go.

 

I'll reinstall a new server manually with our usual settings (English, Norwegian, Norwegian) and try again. I'll get back to you when I've done that.

 

Thanks for taking the time to have a look at this and hopefully i haven't sent us on a wild goose chase. Maybe there was some sort of corruption with that initial server I setup that caused the issue and simply reinstalling (coincidentally with the United States Time and date format) fixed the issue.

Link to comment
Share on other sites

Hi again,

 

Did you reinstall the web server from scratch or just change the culture via Powershell?

 

The reason i ask is because i tried to change the culture on the server installed with the nb-NO culture to en-US via powershell (system wide) . Although it seemed to be applied system wide I still got the error when installing Password state. When i reinstalled the webserver from scratch and choose English - United States at the start of the Windows Setup everything worked as intended.

 

So if your server was installed with en-US as the culture then I would presume everything would still work even if you changed the culture via Powershell to nb-NO.

 

Anyhow.

 

I have now tested by reinstalling the server (2016 server core) with our usual settings (English, Norwegian Bokmål, Norwegian). The database is using the Danish_Norwegian_CI_AS collation as before.

 

Here is a Get-Host of the server.


PS F:\Backup> Get-Host

Name             : ConsoleHost

Version          : 5.1.14393.206

InstanceId       : 05f6fb6c-4a82-41a0-b32b-5e1ff62a2afb

UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface

CurrentCulture   : nb-NO

CurrentUICulture : en-US

PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy

DebuggerEnabled  : True

IsRunspacePushed : False

Runspace         : System.Management.Automation.Runspaces.LocalRunspace

CurrentCulture = nb-NO

CurrentUICulture = en-US

 

This time i didn't join the server to the domain in case there was some strange GPO at work.

 

I then reinstalled passwordstate and i get the same error as before on the admin creation page.


The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

 

I really have no idea. But seeing as you cannot reproduce the error even with a server installed with my culture settings I think we should just cut our losses and move forward.

 

Having the server installed with en-US as the primary culture is fine. As i mentioned above it didn't cause any problems during testing yesterday, so I don't expect any problems going forward.

 

I really do appreciate you taking the time to troubleshoot on your end. But I'm fully aware that Cultures and Languages other than English are not supported.

 

I'll reinstall (yet again :)) with United States as the primary culture and get our new server up and running.

 

Link to comment
Share on other sites

HI Jack,

 

If you're willing, we could provide you a custom upgrade zip file to test i.e. overwrite all your existing files after your initially install Passwordstate. There are two fields I can make a change on here, and then hopefully we can determine what the cause is.

 

Let me know if you're willing to do this, and have the time?

Regards

Click Studios

Link to comment
Share on other sites

Just now, support said:

HI Jack,

 

If you're willing, we could provide you a custom upgrade zip file to test i.e. overwrite all your existing files after your initially install Passwordstate. There are two fields I can make a change on here, and then hopefully we can determine what the cause is.

 

Let me know if you're willing to do this, and have the time?

Regards

Click Studios

No problem.

 

Just let me know what you need done and I'll do it.

 

I'll have to do it later on tonight (my time) as I've got some family business to attend to. I'll send you my email in a private message and you can send me what needs to be done there.

Link to comment
Share on other sites

Hi Jack,

 

Thanks very much - we really appreciate it. This won't be today though, as we need to make the coding changes, recompile, and upload for you. I'll send you a message as soon as we have it ready.

 

Thanks again.

Regards

Click Studios

Link to comment
Share on other sites

1 minute ago, support said:

Hi Jack,

 

Thanks very much - we really appreciate it. This won't be today though, as we need to make the coding changes, recompile, and upload for you. I'll send you a message as soon as we have it ready.

 

Thanks again.

Regards

Click Studios

No rush. Glad to help.

 

Feel free to put debug info or anything else that might be useful. The database is empty, it's a fresh install.

Link to comment
Share on other sites

Hi Jack,

 

Can you do me one more favour? On your version 7 install which was upgraded to V8, does this have the same time/date settings as the failed server? If so, can you go to the Preferences screen, and then 'Authentication Options' tab, and try changing the password for an account - I'd like to know if this fails at all?

 

Thanks very much.

 

Regards

Click Studios

Link to comment
Share on other sites

4 hours ago, support said:

Hi Jack,

 

Can you do me one more favour? On your version 7 install which was upgraded to V8, does this have the same time/date settings as the failed server? If so, can you go to the Preferences screen, and then 'Authentication Options' tab, and try changing the password for an account - I'd like to know if this fails at all?

 

Thanks very much.

 

Regards

Click Studios

Yes, that server is using the same culture and language settings as the new server.


PS F:\Backup\passwordstate_upgrade\passwordstate> get-host





Name             : ConsoleHost

Version          : 5.1.14393.1532

InstanceId       : 5f7a71e2-0fbf-4ea2-9cb7-90f811b499c3

UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface

CurrentCulture   : nb-NO

CurrentUICulture : en-US

PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy

DebuggerEnabled  : True

IsRunspacePushed : False

Runspace         : System.Management.Automation.Runspaces.LocalRunspace

I hadn't updated to the latest bugfix version, so the preferences pane wasn't available (server error).

 

So before I updated I tested changing the user password via the Administration > Users  screen.

It worked fine.

 

Then I updated to the latest bugfix version 8065.

  • I logged in after the update
  • I then clicked on my username and the preferences panel come up (Yay!)
  • I then went to Authentication Options
  • I changed my password
  • Clicked save
  • Got en ERROR - The same error as on the new server
    
    1768	2017-08-17 14:39:18.000	General Error	Error Code = The statement has been terminated.  The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value., StackTrace =    at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)     at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()     at Passwordstate.User.ResetUserPassword(String Password, String FormsPassHistory, Int32 PasswordStrengthIndex)	Error

     

Link to comment
Share on other sites

6 hours ago, support said:

Thanks Jack, and this confirms what we believe the issue is.

 

I've just sent you an email with the new test build, and hopefully this will resolve it.

 

Thanks again for all your help.

 

Regards

Click Studios

Hi,

 

I updated the files are per your instructions, but i still get the same error at the admin creation screen.

This is what i  did

  1. Created a new server
  2. Installed passwordstate
  3. Setup SSL
  4. Checked that the site worked
  5. Stopped the PWS service
  6. Did a manual overwrite of the file with the zip you gave me
  7. Started the PWS service
  8. Went through the setup
  9. Failed with the exact same error on the admin creation screen and was logged in the PWS debug log in the database
  10. I did a reboot of the server and tried again
  11. Same error.

 

 

Link to comment
Share on other sites

Thanks Jack - we do appreciate all your help.

 

We've going to try and setup our own server in a different language, as we need to reproduce this issue ourselves - we don't want to keep asking for your help in testing this.

Hopefully we should have some good news early next week some time.

Regards

Click Studios

Link to comment
Share on other sites

A small recap then:

 

I can reproduce the error with the following server setup.

All system are Windows Server 2016 Server Core

 

WebServer

Language: English - United States

Time and Currency Format: Norwegian - Bokmål

Keyboard: Norwegian - Bokmål (I don't think the keyboard layout matters)

 

SQL Server

Language: English - United States

Time and Currency Format: Norwegian - Bokmål

Keyboard: Norwegian - Bokmål

 

SQL Server default collation: Danish_Norwegian_CI_AS

 

The error is gone on the folowing setup

 

WebServer

Language: English - United States

Time and Currency Format: English - United States  <--- Difference

Keyboard: Norwegian - Bokmål

 

SQL Server

Language: English - United States

Time and Currency Format: Norwegian - Bokmål

Keyboard: Norwegian - Bokmål

 

SQL Server default collation: Danish_Norwegian_CI_AS

 

Conclusion

As it stands, it seems the error is because of the time and date settings used when installing Windows Server.

 

Please don't hesitate to contact me if i can help in anyway.

 

I hope you are able to reproduce this error on your end!

 

 

 

 

 

Link to comment
Share on other sites

Hi Jack,

 

Thanks again, as we've been able to reproduce this issue now. We've made some changes and fixed most of the issues, and only have one small issue outstanding with the Password Reset Portal. So we hope to have a new build available tomorrow to fix this issue once and for all.

Regards

Click Studios

Link to comment
Share on other sites

52 minutes ago, support said:

Hi Jack,

 

Thanks again, as we've been able to reproduce this issue now. We've made some changes and fixed most of the issues, and only have one small issue outstanding with the Password Reset Portal. So we hope to have a new build available tomorrow to fix this issue once and for all.

Regards

Click Studios

This really does show dedication to the product and is immensely appreciated.

 

I'll test the build as soon as it's available.

 

Link to comment
Share on other sites

8 hours ago, support said:

Hi Jack,

 

Just letting you know that we released build 8071 yesterday, which will fix this issue for you. Thanks again for all your help and patience while we figured this out.

Regards

Click Studios

I just installed the new version and everything is working.

 

Thanks again, much appreciate.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...