Issue with using SQL Server domain account when upgrading Jasper Server

[dana.gray]

I'm trying to upgrade our Jasper Server Application utilizing SQL Server. When I provide my username which includes the domain (domainusername) in the default_master.properties file, the js-export.bat script omits the '' when attempting to connect to the SQL Server. This results in the script not connecting to the server and crashing the repository database export. I've attempted using the \ escape for the backspace character but this hasn't seemed to work. What can I do to get this script (and ultimately the upgrade or install scripts) to work with my domainusername dbUserName? Please advise.

 

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Paul Jimenez]

Hello,

 

Can you please clarify what you mean by you using the domain username and password? This needs to be a username and password to your database. Not a domain.

Regards,

Paul

[dana.gray]

Hello Paul,

Our SQL Server is setup to utilize Windows Authentication instead of SQL Server Authentication so the usernames include the Active Directory domain name. Example: stateaccountname. When I add this to the default_master.properties file for the "dbUsername=" the js-export.bat script removes the backslash before presenting it to the SQL Server for authentication. This results in a failed connection which in turn crashes the script. 

I guess the question is, can a username include special characters such as a backslash ''?

 

[dana.gray]

I've discovered that the escape characters needed for the domainusername account to be pushed to SQL Server are two additional backslashes. In other words, the dbUsername in the default_master.properties file should look like domain\username. This will result in the correct username of domainusername when Jasper attempts to connect to SQL Server. I'm now having an issue with the TIBCO SQL Server driver in that even with this correct username it's throwing the following error.

Generic connection failure: [[TibcoSoftware][sqlServer JDBC Driver][sqlServer]Login failed for user 'STATExxxxxx'.]. Treating problem with JDBC connection as unrecoverable.

I'm able to log in using a SQL Server Authentication account but not a Windows Authentication using this same driver. Any ideas as to what the issue might be?

[peter.w]

I am not a network specialist.

You need to be sure that the domain account you are using has SQL access and that your SQL host machine is a member of the domain.
Don't create a local account with the same name as the domain account. (You will then have two seperate user accounts)

 

AFAIK for production systems

You dont want to add the SQL user account to the to the local administrators as this would also grant all administrator privileges in addition to granting SQL access.

You also dont want to add the domain account to SQL "sysadmin" as that would grant all SQL to the domain user.

Ideally you want a  "jasper reports user" with role  level access.

[Paul Jimenez]

Hello,

That is just a generic error that there is still an active session. If you kill that session and try running the upgrade again, is it successful? 

Regards,

Paul