Security Out Of the Box

coldwaterphewServers

Nov 17, 2013 (3 years and 8 months ago)

109 views


Security Out of the Box

Page
1

of
15

11/17/2013

Copyright © 200
8

Information Builders








Security Out Of the Box


Carlotta Cunningham

Information Builders

Information Builders Summit 200
8

User Conference

June

200
8




Author:

Carlotta Cunningham

Company:

Information Builders

Presentation Title:

Configuring WebFOCUS 765
Managed Reporting R
ealm
Driver for SQL Server External Authentication
under the WFRS
and SQL Server
Ext
ernal Authorization

Presentation Abstract:

Adding a twist on my last year’s presentation, I’ve
decided to change the presentation a bit by authenticating to the WebFOCUS
r
eporting server via sql server and still authorizing to sql server. This is security
out of the box at its best. We are securing the reporting server and using that
security for Manage Reporting too.

















Security Out of the Box

Page
2

of
15

11/17/2013

Copyright © 200
8

Information Builders




Components already installed:



Sql Serve
r 2000 sp4



SQL SERVER

JDBC DRIVER
SP3



WEBFOCUS
765



WEBFOCUS REPORTING SERVER
765


Security Out of the Box

Page
3

of
15

11/17/2013

Copyright © 200
8

Information Builders



From the WebFOCUS Reporting Server console (ie
http://localhost:8121/webconsole
)
. Start the server if it hasn’t already been

started.





1.

Click on Data Adapters

2.

Click on New Adapter
/Or add connection under MS SQL Server 2000

(skip
to number 5)

3.

Expand SQL/MS SQL Server/

4.

Select 2000 (UNICODE Optional) by double clicking it.

5.

For connection name enter SOOTB

6.

For server name enter L
OCALHOST

7.

Select the Password Passthru radio button

8.

Enter tomcat for the default database

9.

Click configure

10.

Click on workspace from the dropdown menu and choose Access Control.


Security Out of the Box

Page
4

of
15

11/17/2013

Copyright © 200
8

Information Builders



11.

Choose the Security Mode from the dropdown menu. Choose DBMS.




Security Out of the Box

Page
5

of
15

11/17/2013

Copyright © 200
8

Information Builders



12.

For security_
dbms/security_connection choose SQLMSS
-
MS SQL Server
2000/SOOTB respectively. Notice that SOOTB is the connection we created
above.



13.

The Server Administrator is good. I added that id to
SQL
server
2000
beforehand
.

14.

Click on Apply and Restart. You will b
e prompted with the following
message: This i
ndicates that you have to set a

system variable EDAEXTSEC
to DBMS
be
for
e

starting the server MANUALLY to turn on DBMS security.




Security Out of the Box

Page
6

of
15

11/17/2013

Copyright © 200
8

Information Builders



15.

Define the variable EDAEXTSEC=DBMS in windows, under system
variables.


16.

Once
this is done,
start the server security DBMS




Security Out of the Box

Page
7

of
15

11/17/2013

Copyright © 200
8

Information Builders


17.

When you access the web console again, you will be prompted for a user and
password. This user and password must exist in SQL SERVER. Just to test,
enter ibilab1, ibilab1. That’s the user that the server was

install
ed

under. I
a
dded this user to sql server man
u
a
lly.




From the WebFOCUS administration tool

the default username and password is
admin and the password is blank.


1.

Click on configuration

2.

Click on MR Security Settings

3.

Click on General

4.

Choose the ra
dio button next to External Directory and Authentication.

5.

From the drop down box choose
WFRS
.

6.

Choose the radio button next to External Directory and Authorization

7.

From the drop down box choose
SQLS
.

8.

Click save to save these settings.

9.

You will be prompted w
ith the following dialogue. Click ok.




Security Out of the Box

Page
8

of
15

11/17/2013

Copyright © 200
8

Information Builders


Once that is done, you will need to configure the specific connections to
WFRS and
SQLS

external directories.

1.

Choose External Directories under MR Security Settings

2.

Click on the radio button for
WFRS
.

3.

Click modify
at the bottom of the screen.

4.

You will get a screen for Directory Configuration
-

Modify Settings (as below)


5.

Edit the DR
I
VER

URL, change hostname:port to localhost:8120.

6.

Click save. You will see the following dialogue, just click ok.



Security Out of the Box

Page
9

of
15

11/17/2013

Copyright © 200
8

Information Builders



7.

Click on the radi
o button for SQLS.

8.

Click modify at the bottom of the screen.

9.

You will get a screen for Directory Configuration
-

Modify Settings (as below)




10.

DRIVER.CLASS

stays the same
:



11.

Edit the DRIVER.URL, hostname should be changed to localhost. The new
DRIVER.URL

should look like the following:





12.

The database
tomcat

(in my case)

has already been created in
SQL Server
2000
.

13.

Enter the userid and enter
t
he password.
The

dbowner

stays blank
.

Those
values are tomcat/tomcat.

14.

Click on save.

Clicks ok at this prompt.


jdb
c:microsoft:sqlserver://localhost:1433;Datab
aseName=tomcat
;SelectMethod=Cursor

com.microsoft.jdbc.sqlserver.SQLServerDriver


Security Out of the Box

Page
10

of
15

11/17/2013

Copyright © 200
8

Information Builders




15.

Edit the bat file

D:
\
ibi
\
WebFOCUS76
\
utilities
\
realm
\
realmutil.bat
to add
your
SQL Server
thin client…
three

jars for
SQL Server 2000
. They are
under
C:
\

mssqldrivers
\
lib
. The three

jars are:
msutil.jar, mssqlserver.jar
and msbase.jar.


16.

Edit the bat

file

D:
\
ibi
\
WebFOCUS76
\
utilities
\
realm
\
realmutil.bat
directory
to add the full directory specification for
jconn3.jar

and
jTDS3.jar

for
the
variable
JDBC_DRIVER_JARS


For example:


JDBC_DRIVER_JARS=


JDBC_DRIVER_JARS=

C:
\

mssqldrivers
\
lib
\
mssqlserver.ja
r;C:
\
mssqldrivers

\
lib
\
msutil.jar;C:
\
mssqldrivers
\
lib
\
msbase.jar









Security Out of the Box

Page
11

of
15

11/17/2013

Copyright © 200
8

Information Builders














































rem Copyright © 2004,2005,2006

rem
------------------------------------------------------------------


rem NOTE: loading large script files may require inserting the jvm memory

rem switch to the java comamnd below
. Edit the java line below like

rem this:

rem java
-
mx128m
-
cp ... and so on.


if %OS% == "Windows_NT" setlocal


:start


rem Set JDBC_DRIVER_JAR variable to the full path of your JDBC driver Jar

rem file(s). If your JDBC driver consis
ts of more than one Jar file, separate

rem the multiple jar file paths with a semi
-
colon.

rem For example: JDBC_DRIVER_JARS=C:
\
Program Files
\
Microsoft SQL Server 2000 Driver for JDBC
\
lib
\
mssqlserver.jar;C:
\
Program
Files
\
Microsoft SQL Server 2000 Driver
for JDBC
\
lib
\
msutil.jar;C:
\
Program Files
\
Microsoft SQL Server 2000 Driver for JDBC
\
lib
\
msbase.jar


set JDBC_DRIVER_JARS=
C:
\
mssqldrivers
\
lib
\
mssqlserver.jar;C:
\

mssqldrivers

\
lib
\
msutil.jar;C:
\

mssqldrivers

\
lib
\
msbase.jar


if "%JDBC_DRIVER_JARS%" == "" got
o err


set CLIENTCONFWEBCGI=..
\
..
\
client
\
wfc
\
web
\
cgi


set DEBUG=false


set ROOT=..
\
..
\
webapps
\
webfocus76

set IBI=%ROOT%
\
WEB
-
INF
\
lib
\
util.jar;%ROOT%
\
WEB
-
INF
\
lib
\
webfoc.jar;%ROOT%
\
WEB
-
INF
\
lib
\
gen.jar;%ROOT%
\
WEB
-
INF
\
lib
\
nls.jar;%ROOT%
\
WEB
-
INF
\
lib
\
srv.jar

set
UAS=%ROOT%
\
WEB
-
INF
\
lib
\
uas.jar

set PROPFILELOCATION=..
\
..
\
config


set CLASSPATH=%IBI%;%UAS%;"%JDBC_DRIVER_JARS%"


java
-
cp %CLASSPATH% ibi.uas.config.WFMRX_DBMSUtil %PROPFILELOCATION% %DEBUG% %CLIENTCONFWEBCGI%

goto end

:err

echo JDBC_DRIVER_JARS variable
is not defined

:end

pause


Security Out of the Box

Page
12

of
15

11/17/2013

Copyright © 200
8

Information Builders


After editing the script, run the script by double clicking the file name When prompted
Choose option 1 to create th
e mrrealm tables, ente
r USERNAME
/
PASSWORD

(tomcat/tomcat)
for the user and password than choose 2 to load sample data, lastly X to
exit:



Security Out of the Box

Page
13

of
15

11/17/2013

Copyright © 200
8

Information Builders




Loading sample data…





]











Security Out of the Box

Page
14

of
15

11/17/2013

Copyright © 200
8

Information Builders



Verification that t
he tables were created in SQL Server
:




Sample Data:



Security Out of the Box

Page
15

of
15

11/17/2013

Copyright © 200
8

Information Builders








Please ensure

that your application server has the jdbc jar files. In the case of
TOMCAT

6 this can be done by adding the path and jarfile names do the
WEB
-
INF/lib directories
under
D:
\
tomcat6016
\
apache
-
tomcat
-
6.0.16
\
webapps
\
rcaster and ibi_apps.



You’re ready to try
MRE now.

Open a browser

Open the welcome page my enter
http://localhost:5080/ibi_apps/

in the browser URL
.,


Try out some sample usernames and passwords, like user1, user2, user3, admin..etc. Do
not enter a

password.