cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Changing Database User: CAS

Question is, how to change the user(name) of the database user CAS is using to connect to it's database.
The password can be changed (through Change of CAS under Programs and Features, Control Panel), but not the database user.

Creating a new user in MSSQL, and changing to this user in 'repository.properties' is not enough apparently. CAS shuts down immediately after the log message "Table DeltaInfo not found". Followed by 'Watchdog status is changed from OK to STOP_WRONG_VERSION".

I did re-create the new user exactly as done in the original installation scripts. Made it db_owner.

DeltaInfo exists. But it looks like CAS is not aware.

.

Since we are requested to switch from SQL (local) user account to a Domain user accounts, this may lead to an Article describing this for all the (other) products as well.

6 REPLIES 6

Erik_Soderquist
Dynatrace Pro
Dynatrace Pro

When creating the new user in SQL, you have to give the new user the same default schema and the roles as the old user had. No more and no less.

Also, this specific failure is normally seen when adding the sysadmin role to the CAS SQL service user; having the sysadmin role seems to override the default schema assigned to the user and use dbo instead.

-- Erik

Just reinforcing, the correct default schema required for the CAS (ADS, RUM Console, and CSS too) users is vital.

/**********************************************************************
Purpose: Database schema creation SQL script (isql)
**********************************************************************/
USE [CAS_DB]
GO

if not exists (select * from sysusers where name = 'new_cas_user' and uid < 16382)
begin

create user [new_cas_user] for login [new_cas_user] with default_schema = [new_cas_user]
exec('create schema [new_cas_user] authorization [new_cas_user]')
end
GO

grant backup database to [new_cas_user]
grant backup log to [new_cas_user]
grant connect to [new_cas_user]
grant create table to [new_cas_user]
grant view database state to [new_cas_user]
grant create procedure to [new_cas_user]
grant create function to [new_cas_user]
grant create view to [new_cas_user]

grant SELECT ON sys.tables TO [new_cas_user]
grant SELECT ON sys.objects TO [new_cas_user]
grant SELECT ON sysobjects TO [new_cas_user]
grant SELECT ON sys.indexes TO [new_cas_user]
grant SELECT ON sysindexes TO [new_cas_user]
grant SELECT ON sys.columns TO [new_cas_user]
grant SELECT ON syscolumns TO [new_cas_user]
grant SELECT ON sys.all_columns TO [new_cas_user]
grant SELECT ON sys.database_principals TO [new_cas_user]


RECONFIGURE WITH OVERRIDE
GO

---------------------------------------------------------------------------------
USE master

-- Ensure that the report user is allowed to invoke DBCC command.
GRANT VIEW SERVER STATE TO [new_cas_user]

EXEC sp_addsrvrolemember 'new_cas_user', 'bulkadmin'

Hi,

I created the new user with the script exact as the orginal, with it's own schema. Apparently that did not work.

So, as you stated, I set the same default schema 'cas_db' to the 'new_cas_user'.
I then had to open the schema properties, and under Permissions add the new user under Users, with all Explicit grants, except 'Alter' and 'Take Ownership' (Although that is shown in Effective as well).

This seem to work, but wonder if this is the right approach.


Additional info, changing from local/SQL user to a Windows Domain user.
The above schema settings needs to be applied to the domain user, that need to be available.

From SQL Authentication:
JDBC_URL=jdbc:jtds:sqlserver://localhost:1433//CAS;useNTLMv2=false;tds=8.0;lastupdatecount=true;
JDBC_USER=cas_user
JDBC_PASSWORD_ENC=

To Windows DOMAIN account:
JDBC_URL=jdbc:jtds:sqlserver://localhost:1433//CAS;Domain=YOURDOMAIN;useNTLMv2=false;tds=8.0;lastupdatecount=true;
JDBC_USER=cas_domain_user
JDBC_PASSWORD_ENC=


Please note; a database password change trough CAS method resets ALL occurences in the file. Even is commented-out.

Please, use "domain" instead of "Domain" in the JDBC_URL for Windows DOMAIN account. This will make future upgrades easier.