Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

Custom DB password in WCS 7.0.220.0

Hi there,

we are desperately trying to set a custom password of our WCS database in order to use it for direct SQL queries (Cumbersome over Web surface).

To my knowledge there is a way to reset it however this password would be randomly generated and not available in plain text.

Until version 6 there was a feature to directly set a password via the dbadmin command.

Anyone know a way? Thanks!

Regards,

Patrick

Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
New Member

Re: Custom DB password in WCS 7.0.220.0

Hi Patrick,

Update (2012/07/26, 13:07 UTC):

Please mind my add-on to this post below.

For a properly working WCS service it is also required to update the dbopts.db file, that contains the password for the user dba in encrypted format.

Otherwise the WCS service will fail connecting to the solidDB.

Fortune favors the brave!

The password for the DB user 'dba' can be easily changed by using the "Diagnostics" of the WCS, that are accessible through "https:///webacs/wcsDiag.do". Choose "DB Update" or use the direct link "https:///webacs/dbUpdateAction.do" and enter the SQL statement ''ALTER USER dba IDENTIFIED BY ". Then click on "Run Update" to have the password changed.

After doing so, you can run any queries directly from the server CLI (See below).

[root@WCS bin]# more /ftproot/sqlquery.txt

select * from WCSDB.MODB.MACVENDOR where MACOUI = '00:00:01';

[root@WCS bin]# pwd

/data/WCS/WCS7.0.220.0/webnms/db/bin

[root@WCS bin]# ./solsql 'tcp localhost 1315' dba dba /ftproot/sqlquery.txt

Solid SQL Editor (teletype) v.04.50.0184

(C) Copyright Solid Information Technology Ltd 1993-2010

Connected to 'tcp localhost 1315'.

select * from WCSDB.MODB.MACVENDOR where MACOUI = '00:00:01'

MACOUI             COMPANY_ID        

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

00:00:01           XEROX CORPORATION 

1 rows fetched.

SOLID SQL Editor exiting.

Please note that the table 'WCSDB.MODB.MACVENDOR' is a custom table but obviously queries can be run for any other table in the WCS DB as well.

HTH

---

Stephan

12 REPLIES

Re: Custom DB password in WCS 7.0.220.0

Patrick:

To my knowledge you can reset the password and specify your own password using dbadmin:

See here: www.cisco.com/warp/public/102/wcs-dbadmin-commands.pdf

If i remember correctly, the db password set first time aytomatically and generated randomly again if you do reinitialize the db. (or there was a timeout after which db passwrd automatically reset).

I've seen a bug long time ago due the db password reset and the work around was to do something (db initialization, db password reset...etc. forgot what exactly) that cause db password to be generated.

HTH

Amjad

Sent from Cisco Technical Support iPad App

Rating useful replies is more useful than saying "Thank you"
New Member

Re: Custom DB password in WCS 7.0.220.0

Hi Amjad,

the method you suggest does not work anymore in WCS version 7. It was working up until WCS version 6.

I have already searched numerous times for an answer, but somehow I have the feeling that Cisco keeps a low profile on this topic as to why this feature has been removed.

Is there no other solution out there?

Re: Custom DB password in WCS 7.0.220.0

Hi Patrick,

I got you now. Sorry for not understanding the point from your first post.

I remember now that the bug I watched was like WCS upgraded after changing the db password. so for those users that upgrade from 6 to 7 WCS they got a password changed. they had to do something in order to get the password automatically generated.

I would suggest something but I don't know if this works:

if you brought the dbadmin.sh (or dbadmin.bat in windows) from older version and put it in windows 7 WCS bin folder that should make the db password change available. right? You may probably be able to use it then. They may not prevented the internal system from accepting the command, but they would probably removed it from the dbadmin script while it is still allowed if it is triggered.

You may also open the dbadmin file (in 6.0 or earlier) in text and see what does happen and what files are used when you call the db password change option.

HTH

Amjad

Rating useful replies is more useful than saying "Thank you"
New Member

Re: Custom DB password in WCS 7.0.220.0

Hi Patrick,

Update (2012/07/26, 13:07 UTC):

Please mind my add-on to this post below.

For a properly working WCS service it is also required to update the dbopts.db file, that contains the password for the user dba in encrypted format.

Otherwise the WCS service will fail connecting to the solidDB.

Fortune favors the brave!

The password for the DB user 'dba' can be easily changed by using the "Diagnostics" of the WCS, that are accessible through "https:///webacs/wcsDiag.do". Choose "DB Update" or use the direct link "https:///webacs/dbUpdateAction.do" and enter the SQL statement ''ALTER USER dba IDENTIFIED BY ". Then click on "Run Update" to have the password changed.

After doing so, you can run any queries directly from the server CLI (See below).

[root@WCS bin]# more /ftproot/sqlquery.txt

select * from WCSDB.MODB.MACVENDOR where MACOUI = '00:00:01';

[root@WCS bin]# pwd

/data/WCS/WCS7.0.220.0/webnms/db/bin

[root@WCS bin]# ./solsql 'tcp localhost 1315' dba dba /ftproot/sqlquery.txt

Solid SQL Editor (teletype) v.04.50.0184

(C) Copyright Solid Information Technology Ltd 1993-2010

Connected to 'tcp localhost 1315'.

select * from WCSDB.MODB.MACVENDOR where MACOUI = '00:00:01'

MACOUI             COMPANY_ID        

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

00:00:01           XEROX CORPORATION 

1 rows fetched.

SOLID SQL Editor exiting.

Please note that the table 'WCSDB.MODB.MACVENDOR' is a custom table but obviously queries can be run for any other table in the WCS DB as well.

HTH

---

Stephan

Re: Custom DB password in WCS 7.0.220.0

Stephan,

Fantastic! Thanks for the info.

Can you please confirm if changing the dbadmin file from older version and replacing it on WCS bin directory make it possible to change the password from dbadmin itself?
I suppose the same SQL query is being made so that should somehow work.

Thanks.

Amjad

Rating useful replies is more useful than saying "Thank you"
Hall of Fame Super Silver

Re: Custom DB password in WCS 7.0.220.0

Good info!

Sent from Cisco Technical Support iPhone App

-Scott
*** Please rate helpful posts ***
New Member

Re: Custom DB password in WCS 7.0.220.0

Hi Stephan,

thanks for the info.

@Amjad: I tried extracting the script from the binary of WCS 6.0.196.0, but ran into some issues doing so.

As Stephan already provided a working solution I abandoned further attempts.

Thanks again!

Regards,

Patrick

New Member

Re: Custom DB password in WCS 7.0.220.0

I tweaked the process of querying the database by avoiding the step to create a file containing the actual query command.

Just take the query command and directly put it into the solsql command with the -e parameter and enclose the query command with double parenthesis (Note that the semicolon at the end of the query command needs to be ommited!):

e.g.

[root@WCS bin]# ./solsql -e "select * from WCSDB.MODB.MACVENDOR where MACOUI = '00:00:01'" 'tcp localhost 1315' dba

Solid SQL Editor (teletype) v.04.50.0184

(C) Copyright Solid Information Technology Ltd 1993-2010

Connected to 'tcp localhost 1315'.

MACOUI             COMPANY_ID        

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

00:00:01           XEROX CORPORATION 

1 rows fetched.

SOLID SQL Editor exiting.

New Member

Re: Custom DB password in WCS 7.0.220.0

Hi all,

I hope not too many have followed my approach yet as it comprises a problem. That is leaving the dbopts.db

file (that contains the encrypted password for the 'dba' user) in the directory /WCS7.0.230.0/webnms/db/eval_kit/standalone unmodified which will no longer allow to start the database server if the WCS service is re-started due to invalid credentials or mismatch between database user record and the password in dbopts.db file.

You would find similar messages as shown below in the logfile solmsg.out

[root@WCS standalone]$ more solmsg.out

23.07 14:36:52 User 'DBA' was disconnected abnormally, user id 39, machine id WCS.

23.07 14:36:52 User 'DBA' was disconnected abnormally, user id 53, machine id WCS.

23.07 14:36:52 Memory allocation size has fallen below 1483MB. Current size: 1483647331 bytes. Number of allocations: 12465.

23.07 14:36:56 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 14:38:04 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 14:46:17 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 14:49:52 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:03:56 Server shut down by either ALT+F4 or kill command

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

Mon Jul 23 15:09:36 2012

Version: 04.50.0184

Operating system: Linux 2.6.18 ix86 MT

Solid BoostEngine - v.04.50.0184 (Linux 2.6.18 ix86 MT)

(C) Copyright Solid Information Technology Ltd 1993-2010

Using license file /data/WCS/WCS7.0.220.0/webnms/db/eval_kit/standalone/solid.lic

License for Solid BoostEngine 4.x, Standard Edition

Development license

Serial number: 416000266

     4 processors

     100 concurrent connections

     25 threads

     SmartFlow option

     HotStandBy option

     1 master databases

     5 replica databases

     Accelerator option

     Diskless replica option

Licensed to:

     Cisco Systems

     WCS WNBU

Current working directory changed to /data/WCS/WCS7.0.220.0/webnms/db/eval_kit/standalone

Using configuration file /data/WCS/WCS7.0.220.0/webnms/db/eval_kit/standalone/solid.ini

Starting roll-forward recovery, please wait ...

Recovery of 2242 transactions successfully completed

23.07 15:09:57 Listening of 'TCP/IP localhost 1315' started.

23.07 15:09:58 Solid BoostEngine started at Mon Jul 23 15:09:58 2012

23.07 15:09:58 Database started.

23.07 15:09:58 Memory allocation size has exceeded 1102MB. Current size: 1102911836 bytes. Number of allocations: 738.

23.07 15:09:58 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:14:58 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:19:58 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:24:58 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:29:58 User 'DBA' tried to connect from WCS with an illegal username or password.

23.07 15:34:58 User 'DBA' tried to connect from WCS with an illegal username or password.

In order to avoid that problem please follow the steps below:

Please note, that the first two steps are only necessary if you did already change the 'dba' user password with the SQL statement ''ALTER USER dba IDENTIFIED BY ") and only replacing the dbopts.db file does not resolve the issue of mismatching credentials.

1)

Uninstall the WCS 7.0.220.0 installation

2)

Conduct a fresh installation of WCS 7.0.220.0 (or successor version 7.0.230.0).

Restore from an existing database backup (http://www.cisco.com/en/US/docs/wireless/wcs/7.0/configuration/guide/7_0main.html#wp1077207).

3)

Stop the WCS 7.0.220.0 (or 7.0.230.0) service

Update (2012/07/25, 08:18 UTC):

If the database server cannot be stopped by using the /opt/WCS7.0.30.0/StopWCS script, navigate to the /WCS7.0.230.0/webnms/db/bin directory and connect to the database server with the command "./solcon 'tcp localhost 1315' dba ". Issue the command "shutdown" in order to stop the database service. You will be automatically disconnected from the database server. Stopping the WCS service by using the /opt/WCS7.0.30.0/StopWCS script will now work and you can proceed with the next step.

4)

Conduct a fresh installation of WCS 6.0.202.0.

Start the WCS 6.0.202.0 service.

Run the dbadmin.sh utility and change the database password for the user 'dba' (./dbadmin.sh password ).

This will update both the corresponding user record in the database itself as well as the dbopts.db file.

5)

Stop the WCS 6.0.202.0 service.

Start the WCS 7.0.220.0 (or 7.0.230.0) service.

6)

Run the SQL statement as described in my previous post.

7)

Make a backup of the dbopts.db file of the v7 installation and replace it with the one of the v6 installation.

[root@WCSstandalone]# pwd

/data/WCS/WCS7.0.230.0/webnms/db/eval_kit/standalone

[root@WCS standalone]# cp dbopts.db dbopts.db.backup-v7

[root@WCS standalone]# rm -rf dbopts.db

[root@WCS standalone]# cp /data/WCS/WCS6.0.202.0/webnms/db/eval_kit/standalone/dbopts.db .

8)

Restart (Stop and start) the WCS 7.0.220.0 (or 7.0.230.0) service.

By doing so, the WCS will continue working without any problems and direct database access is possible.

[root@WCS bin]# /opt/WCS7.0.230.0/WCSStatus

Health Monitor is running.

WCS is running.

Database server is running

Apache server is running

[root@WCS bin]# ./solsql -e "select * from SYS_USERS" 'tcp localhost 1315' dba

Solid SQL Editor (teletype) v.04.50.0188

(C) Copyright Solid Information Technology Ltd 1993-2010

Connected to 'tcp localhost 1315'.

       ID NAME               TYPE                    PRIV PASSW           PRIORITY   PRIVATE LOGIN_CATALOG    

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

        1 DBA                USER                       1       NULL         0 WEBNMSDB         

1 rows fetched.

SOLID SQL Editor exiting.

Please apologize my delayed update to this post!

HTH

---

Stephan

Re: Custom DB password in WCS 7.0.220.0

Stephan:
Sorry to hear that.

One more thing: won't it work if I brought the dbadmin file from 6.x version and replaced the one ine 7.x versoin? the dbadmin file in 6.x contains the needed script to change the DB password, right?

Rating useful replies is more useful than saying "Thank you"
New Member

Re: Custom DB password in WCS 7.0.220.0

Hi Amjad,

No, I'm afraid it won't. This was also my first approach, but replacing the dbadmin.sh file will not help as the script itself is the same for both versions and only links to other resources. You really have to change the encrypted password in the dbopts.db file using the dbadmin utility under version 6.x and copy the file over to the version 7.x directory.

--

Stephan

Custom DB password in WCS 7.0.220.0

Thanks Stephan.

Rating useful replies is more useful than saying "Thank you"
1967
Views
15
Helpful
12
Replies