Can the instructions be updated to clarify a couple of items please? 1)
when configuring the SQL Server database connection to use Windows Authentication, this will ignore the username and password fields in the configuration file and uses the run-as account for the PaperCut app server service. 2) the db-tools commands will use the windows logged-in user permissions to run this tool (again, not the user in the config file) so you will need to run the command line interface as the correct user. 3)After running the init-db and then the import-db command, the import-db command errored stating that the database was not empty, and asked that we use the force option. Including that syntax in the step would be helpful and also mentioning if this is expected behavior.
Thank you for providing these guides and for detailing the steps as much as possible. This makes it so much easier to perform these types of tasks.

If you happen to have a Windows "service" account that didn't have "login" rights like I was provisioned for my servers, I had to use Sysinternals PSEXEC to open a command prompt as that account that was *also* elevated, in order to use the initialize/import database commands above.

1. Open an elevated command prompt.
2. psexec -u <domain>\<user> -h cmd

Use whoami to verify you are that user. And then perform some simple elevated command to ensure you have the necessary permissions, ex: mkdir "%WINDIR%\test", rmdir "%WINDIR%\test"

Thanks for sharing that information! We'll update the knowledge base article as requested.
cheers,

Hi,
We got an error when trying to initialize the new database (Step 6) during the upsizing process, and the only solution we found was to alter the default database of the MSSQL SERVER user.

We entered the correct credentials on PaperCut's server configuration .txt
#database.url=jdbc:sqlserver://localhost:1433;databaseName=
#database.username=
#database.password=

How can we make it work without altering the default database since we already defined it.

Hi Alex,

Thanks for reaching out!

In order to dig into the details of your specific situation, can you please submit a ticket with us at support.papercut.com, so we can investigate this issue further.

Cheers!

Hi Team,
I have a customer who like to deploy their PaperCut infrstructure within AWS (Amazon Web Services) Landing zone. they would like to use Amazon Relational Databases Services (RDS) using Microsft SQL servers. Does PaperCut support AWS RDS/SQL as a service?
cheers
Tariq

Hi Tariq!

When it comes to supported database platforms, we list these as part of our system requirements for PaperCut. https://www.papercut.com/pr.... If a cloud provider is able to offer a hosted version of one of these databases (ie. SQL Server) then theoretically it should be possible to connect PaperCut to one of these instances. However, when looking at an RDBMS we recommend keeping this as close as possible to the Application Server. So, keeping both components within the same zone and testing the connection speed and latency between the two is going to be important. Also, making sure the platform is highly available is important, as without the database, PaperCut will be unable to function. If you'd like to get into any further detail, feel free to reach out to the team: https://support.papercut.com. Cheers!

Thanks for the above reply although its raises another Q, for High availibility I am prposing to have 2 App server (Active/Passive) and 2 node SQL server cluster across two AWS zones. so if one App server or a SQL server goes down, the automatically takes over. Now ther is a possiility that at some point the Active App server and the SQL server (assuming one node is down) are within different zones.
Do you see this as a risk or issue?
cheers
Tariq

Hi Tariq,
I have turned this into a ticket so we can dig deeper into your needs and discuss your particular environment.
Regards,
Steven.

Hello,
We're trying to move the PaperCut Database to an Azure SQL Instance but are running into some troubles that might be related to what is installed with PaperCut. With what we believe are the right connection strings, we get an error "com/microsoft/aad/adal4j/AuthenticationException". This leads us to think that either the MSJDBC driver is not the latest version or that the MS Authentication Library for java is not installed (or up to date) or both. Does anyone have any thoughts on this? We must use TLS, we must NOT use SQL auth, and we must use Azure SQL. I should add that we are running the latest version (22) and Windows Server 2019.

I am assuming this isn't supported, but I was able to get this working. I downloaded the msal4j library and all of its dependencies and copied them into "[app path]\server\lib" and then renamed the included MSSQL-JDBC driver and installed the newest one from Microsoft, mssql-jdbc-10.2.1.jre11. I tested the connection to the database and SUCCESS! We were able to backup the internal DB, connect and build the external DB in Azure and then import and run successfully.

The connection string we used is:

jdbc:sqlserver://[AzureInstanceName].database.windows.net:1433;databaseName=[DBName];Authentication=ActiveDirectoryPassword;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;encrypt=true;ssl=require;sslProtocol=TLSv1.2

I don't know what will happen with updates, but perhaps these items can be included in the next release for others to use in a more supported fashion.

Hi Jason,

Thank you for reaching out on this!

I'm sorry to hear you initially ran into some trouble but I'm glad to hear you go this working. We have a feature request to officially support Azure SQL, though in testing it should work out of the box. In order to dig into the details of your specific situation can you please submit a ticket with us at support.papercut.com. Establishing a direct line of communication will help us determine whether this is a supported solution that needs documenting, as well as looking into logging a feature request to include the libraries you've implemented.

Thank you!

Hi all,
I have an error message that redirected me to this web page, but I don't know what I need to do to solve the problem. The error message is :
"The driver used to connect to SQL Server is outdated. Refer to https://www.papercut.com/su... for instructions for switching to the current driver."
We are using an external SQL Database. Papercut version : 20.1.3 (Build 56199)
Thanks for your help.
Stephane

Hi Stephane,
Thanks for getting in touch.

The settings you need to change are below.
https://uploads.disquscdn.c...

If you have any further questions, please reach out to us over at https://support.papercut.com

Thanks
Dan

Hi All,

I do not mind much because it is a new install (there is no data except the basic setup and health API key that I would like to bring back at this point) but I do not like to get that type of problem early in the setup process.

Either for init-db or import-db , As soon as the "-f" is specified I get to that message and it will hang forever and do nothing on the database:
"IMPORTANT: Ensure the application server is stopped before proceeding. WARNING: The import-db force option has been specified. This will delete the existing database before importing it."
Even if all the table and view are Drop and init-db can be use without the "-f".
If I use "-f" it hang, If I don’t it work.

The problem is that there is no way to use import-db with that issue.

Papercut (pcmf-setup-21.1.1.57908) on Windows2019.
External DB on MSSQL server 13.0.5882.1.
User owner of the DB

.\db-tools.exe init-db -f
.\db-tools import-db -f 'D:\DBBACKUPTEST.zip'

Any idea?

Thank you

Hey Michel,

Thanks for reaching out to us on this one.
I just want to make sure I'm understanding your question correctly. You've got a DB backup that you want to import (DBBACKUPTEST.zip). And when you run the init-db and import-db commands with the force tag, it hangs?
If I'm right, awesome. It's a pretty simply solution. After the warning you mentioned, it should ask if you want to continue. simply type y and press enter. That should get it going.
If that doesn't work, it might be worth opening a support ticket with us at support.papercut.com so we can look into it with you.

Cheers,
Josh

BINGO. The "Do you want to continue? (y/N)" did not show up in my powershell ISE. That was an easy resolution ;)

Please note when you import the DB it has to be the full path of the file, enclosed in quotes. The KB makes you think you can simply enter the file name. Also, as of v20.3, you have to use the -f option.

v20*

Hi Kyle, thanks for the feedback! We will review this page and update accordingly. Thanks!

I get the following error while updating the server:

" Error occurred running db-tools, command: import-db"
"java.time.LocalDateTime cannot be cast to java.lang.String"
Any help in the matter will be appreciated

You didn't give us much detail on your configuration, but likely you are using MySQL and encountering a known bug: https://bugs.mysql.com/bug....

Basically the remediation is to downgrade MySQL to 8.0.22

If you are still having problems after the revert to 8.0.22 then head over to support.papercut.com and open a support ticket. We'll gather more information from you there and see how else we can help. Cheers!

Hi Sean,

Yes I'm using MySql. I'll try your advice first and log a ticket as recommended if I don't come right,

Many thanks...

Just in case this catches anyone else out.....

When trying to connect to a SQL instance the guide about says:

jdbc:sqlserver://server\instanceName:port;databaseName=database;socketTimeout=600000

Where there is only 1x \ between the servername and the instance name.

This wouldn't work for me, I had to use 2x \\ so string was:

jdbc:sqlserver://server\\instanceName;databaseName=database;socketTimeout=600000

That was driving me nuts!

Hi all not sure if this is the correct forum. But when I go to do the import it i saying the database is not empty and that I need to use the force command. Is this normal behavior ? I am in the process of migrating from the internal DB to a MSSQL

Hi Peter,

Thanks for reaching out! It looks like you might have spotted an error in these instructions; so long as you've followed all the preceding steps as directed, you should be fine to use the force flag (-f) as follows:

db-tools import-db -f "backup file name"

I believe the reason the database is not technically empty after initializing it in Step 6 is because part of the initialization creates initial application data, such as template records for Shared Accounts and Printers. Overwriting this data by using the force flag is fine, because the database export you created from your internal database already contains this data.

I'm going to have the documentation altered to reflect this :)

If you're still having issues after inserting the force flag into your command, raise a request through our Support Portal and we can help you out!

Just did a migration on 20.1.3 MF and can confirm it did require the -f (force Flag)

Is there a way to upsize the job-ticketing database as that doesn't seem to get covered with this article.

is there any way to migrate the job ticketing database to an external server?

Hi,
Getting:
Error occurred running db-tools, command: init-db.
An error occurred initializing the database. Error: Wrong number of parameters: expected 2, was given 1 Query: insert into tbl_sequences (sequence_name, sequence_next_hi_value) values (?, 1) Parameters: [tbl_account]
Using PaperCut 19.1, WinServer2019 and Sql 2008.
Multiple tables are created but blank.
Domain user DOES have full access.
Any ideas?
Thanks.

Hello,

On an issue like this it would be great if you could head over to support.papercut.com and open a support so we can get more detailed information and have a conversation. Some of the information we need is the full init-db command you are executing, are you in the process of migrating from Derby, and if this is new database being stood up? Once we can get a support ticket started with you we can see how we can get this problem resolved for you. Thank you!

The information for using SQL Server instances is incorrect. We just moved our SQL last night and using this site: https://docs.microsoft.com/... i was able to correclty determin the string.

Step 5 part 5 with SQL Server instances says the connection string should look like this:
jdbc:sqlserver://[server][\instanceName][:port];databaseName=[database];socketTimeout=600000

Microsoft says it should look like this:
jdbc:sqlserver://[server];instanceName=[instanceName];databaseName=[database];socketTimeout=600000

This is what we did and the connection worked.

Hey Mike,

Thanks the feedback on this! It looks like the primary difference is that our example has the instance name in-line with the server name but your string has it declared on it's own as instanceName=. This matches up with the SQL documentation you linked, and if it works then you should be OK to continue using that string!

I did some testing with a colleague and while we were able to use the connection string that used the recommendations from this article, I am wondering if there is some environmental variable that would prevent that method from working in certain configurations. Are you able to provide a bit more detail about how you have your SQL server configured with us over at support.papercut.com? We would like to understand why this particular string was required in your environment as it sounds like something we will want documented!

Thanks,

Hi,

Just wanted to add in that I was also unable to get it to work based on the details in the article. I ended up changing it to :

database.url=jdbc:jtds:sqlserver://[HOSTNAME]/[DATABASENAME];instance=[INSTANCENAME]

I'm sure that is what this article used to say as I had to refer to an older test system to get the syntax and I would of been here following these steps to do it.
My setup was on v19 using SQL 2017 on a remote server.

Hey Doug,

It's possible it might be worth discussing this further on a ticket with us over here, but I think the issue is that you're using the older syntax with our older in-built Derby driver due to the use of :jtds: in your string. We now by default use Microsoft's SQL driver for significantly better performance as per our release notes on v19.0.1 onwards, see references PC-14525 & PC-16244.

You'll also need the socketTimeout=600000 change on the end of your string too due to the time metric changing between the two database drivers.

I was able to make it work using:
database.url=jdbc:sqlserver://[server]\\[instance]:[port];databaseName=[db];socketTimeout=600000
Seems to me like the backslash gets skipped as if a one line command? So using 2 for separating server and instance displayed ok when running command...

Moved to SQL and everything is running ok on v19.0, but getting a warning message in logs about sql driver out of date, but link offers no information about driver. Log Message: "The driver used to connect to SQL Server is outdated. Refer to https://www.papercut.com/su... for instructions for switching to the current driver."

Hey Austin,

Thanks for posting!

Since you're on SQL Server, you'll want to take a peek at the SQL Server specific page. Apologies for the confusion there! I'll see if changing that link to your specific environment's link is something we can do! There's instructions for versions both 19.0+ and 18.3 and below:
https://www.papercut.com/su...

If you have any questions about getting your external database up and running, please let us know at support.papercut.com!

Best,
-T

Hello,
I used the following config in the server.properties and got a strange error when performing init-db

database.type=SQLServer
database.driver=net.sourceforge.jtds.jdbc.Driver
database.url=jdbc:sqlserver://[name of my server][:port];databaseName=[name of my DB];socketTimeout=600000
database.username=[name of DB user]
database.password=[pass of DB user]


C:\Program Files\PaperCut MF\server\bin\win>db-tools init-db
Runnning db-tools
Version: 18.0.4 (Build 43116) Schema version: 40
Database: jdbc:sqlserver://bewinort101.ortis.int:1433;databaseName=papercut;socketTimeout=600000;sendStringParametersAsUnicode=false
Driver: net.sourceforge.jtds.jdbc.Driver
Type: SQLServer

Error occurred running db-tools, command: init-db.

An error occurred initializing the database. Error: Échec de l'ouverture de session de l'utilisateur '[name of DB user]'. Raison : le mot de passe du compte doit être changé. ClientConnectionId:427195de-396d-4dff-b92d-0ddf29aa73c3

Please check the database connections settings and that the database user
has full permissions to the access database.

Then re-run the initialization using the 'init-db -f' option to force the
database to be recreated.
See server.log for full error details


Which translates into: "Failed to open a session for user '[name of DB user]'. Reason: account password must be changed.

So, two question:
- is my server.properties config correct ?
- any idea about this "password must be changed" error ?

Hey Sixpie Piesix!

It looks like the account used to administer the database is either different or has an incorrect password.

If you could raise a support request with us by emailing the above information to support@papercut.com we would be more than happy to help troubleshoot this with you!

Alternately you can also raise a ticket HERE.

Hi!
Does PaperCut store/manipulate data using NVARCHAR/NCHAR?
Asking to clear up wether the choice of Collation will have any large impact on a global PaperCut installation with multiple languages involved.

Thanks in advance!

Hey Timmy,

We recently made a change for our database schema that might help with your requirement;

9 April 2019 - PaperCut MF 19.0.1 (Build 49140)
Fixes:
"Updated the database so SQL server can accept and store special characters from character sets other than Latin (for example, characters with umlauts or accents, .Arabic characters etc.)."

I'm not sure however if we're using NVCHAR or NCHAR for this specifically. I believe it applies to all fields within the schema though. You might want to contact us over at our support page so we can double check this with you if you would like.

Is there a way to hash / salt / encrypt the password used for connecting to the database in a manner similar to how the admin account password is hashed after entry?

Hello Kevin,

Thank you for reaching out to us! Sadly, the db password cannot be hashed like our admin UI password. If you are running on a external SQL database there may a solution, but I would open a help request at support.papercut.com and go over what you are trying to accomplish. We may be able to come up with a solution once we get more information. We look forward to hearing from you!

I followed this exactly step by step on a new MS SQL sever with no other databases, but when I got to the last step to import, it said:

Starting database import
Error occurred running db-tools, command: import-db.
Database is not empty. To delete the data before loading use the 'force' option.

Is this because of step 6? Anyway, is the force option safe to use? I assume it means deleting data from the NEW database (which should be empty anyway) and not the current? I don't want to lose any data. Thanks.

Hi JJJones,

Yes, when you run step 6, it will create the tables within the database by running -f it will double check these are all empty from any data. When you run step 7, it will then import your backup from step 2.

If you have any further questions, please get in touch at support@papercut.com and we we will be there.

Cheers
Dan

Is this the same process for MF? If something were to go wrong, does simply reversing Step 5-2 and restarting bring it back to the internal database? Thanks.

Hi JJones,
The process is identical for both NG and MF, and if you do need to move back to the internal database because of problems you are 100% correct just reverse 5-2.
If you have used your external database for a little bit and need to keep the data, simply back up your database as per Step 2, reverse Step 5-2 and import your database again. :-)
I would also highly recommend bringing in your PaperCut reseller as well, as should something not quite go to plan they should be able to help get you running again...

SQL Server Express DB was give a full access right and port 1450 already opened......but still got this error

Error occurred running db-tools, command: init-db.

An error occurred initializing the database. Error: Network error IOException: C
onnection refused: connect

Please check the database connections settings and that the database user
has full permissions to the access database.

Hi Parry,

Can you try opening port 1433 and also double check the settings in the SQL Config Application to make sure you have set the correct IP as being active, If you don't do this SQL will not accept the connection. As a quick test you can use either the ODBC datasource tool that is part of Windows or use Telnet to connect to the port to make sure it is opened.

If you don't get anywhere after checking this can you send an email to support@papercut.com and I will help you from there.