How To Set Up Centralized Security Policy Management Across Multiple Remote Proxy Servers With SafeSquid

Enterprises with multiple geographically distributed locations, face challenges in enforcing a universal Internet Access Policy across the enterprise. Usually, each location deploy their own preferred  content filtering solution, and hence can not synchronize the Corporate Internet Access Policy (CIAP) globally. SafeSquid's Multi-Proxy or Master-Slave feature allows you to enforce your CIAP universally across the enterprise, irrespective of the geographical location of a remote office. Master-Slave configuration ensures that policies are enforced globally, and that any changes made to the policies on the Master SafeSquid server, get synchronized to all the Slave servers across the Enterprise, in real-time, thereby greatly reducing an Administrators management overheads. SafeSquid's Master-Slave feature is so granular, that you can define unique user authentication mechanism for each location, unique access policies for a specific location, exclude or include locations a policy should apply to, create granular access policies based on the group a user belongs to, and much more. Master-Slave can be implemented, irrespective of whether the Enterprise has a common Internet Gateway or a distributed one.

Master-Slave in a central gateway scenario

Master-Slave in a distributed gateway scenario

A Master server can be set up in the normal way you would set up a stand alone server, and you do not have to do anything extra during the installation, except ensure that the Slave servers are able to access SafeSquid interface, either on a private internal connection, or via the Internet. For allowing access to SafeSquid Interface via Internet, you will have to make the Master server listen on a static (live) internet IP. Next, you need to make the Master server listen on an additional port on which to allow access only to the SafeSquid interface, other than the port on which it will server the users. Then create entries in the Access Restrictions section, to allow the slave servers to access the interface. If the remote locations have static internet IPs too, it would help in securing access to the Interface based on the IP of the locations, and not from anywhere else.

Suppose the Master SafeSquid is listening on port 8080 (default). Now, you need to make it to listen on port 8081 too, and allow access only to the web interface on this port. To make SafeSquid listen on port 8081, go to Config > Network settings and create the following entry -

Next, click on 'Save settings' in the Top Menu of the interface, save the settings, and restart SafeSquid service. When SafeSquid starts this time, it will listen on both, port 8080 and 8081. Next, you need to create an entry under Access Restrictions, to allow access only to the SafeSquid interface, on port 8081.

The above entry means that the proxy will accept requests on the specified interface (IP=152.23.163.10 which is supposed to be the static IP, and PORT=8081) from the specified source 'IP Address' (from any source IP if left blank) and allow them access to the web interface (Access=config). It will additionally apply a profile 'SLAVES' to such requests.

Note: The entries in Access Restrictions are applied from top-to-bottom hierarchy. The first entry that matches is applied, and the rest are ignored. Hence, all IP based entries should precede non-IP based rules, else the entry will never be applied.

At each of the remote locations, while installing SafeSquid, you will need to define the IP:PORT of the Master server, from where the Slave can fetch the configuration file, for synchronization. based on the above example, this would be 152.23.163.10:8081, the IP and port that is configured on the Master server to allow access to Slave servers.

Master Proxy Configuration Section in SafeSquid for Windows Installation

Synchronization Time Setting in SafeSquid for Windows

Master Proxy Configuration Section in SafeSquid for Linux Installation

Synchronization Time Setting in SafeSquid for Linux

By default, these values are a blank in both SafeSquid for Windows and Linux. You need to change this to 152.23.163.10:8081. The polling interval in seconds, at which the Slave should request configuration updates from the Master server, is by default set to 60 seconds. You can change it to any acceptable value.

Another point to note during the installation, is to define a unique HOSTNAME for each SafeSquid Slave. You can either specify the HOSTNAME during the installation, or leave it blank, and later specify it from the SafeSquid Interface > General Section. Specifying a unique HOSTNAME will help you later, for defining granular policies based on these hostnames.

Once done, when the SafeSquid Slave server starts, it will fetch the configuration file from the Master Server, and then update itself again every 60 seconds. You can verify if the Slave is properly synchronizing with the Master, from the 'View log entries' in the Slave web interface. You should find entries similar to the below screen shot -

You can use the unique HOSTNAME of a Slave server, to create granular polices, that should apply to specific Slave servers. For example, if you would like a Slave server, with hostname PROXY3, to authenticate users from an LDAP / Active Directory Server within its network, all you need to do is specify the hostname of slave server in the entry that you create in the LDAP Configuration section, with its local authenticating server details. This way, you can configure a unique authenticating server for each slave server. 

Similarly, you can create unique profiles too, that should apply to specific slave servers, by defining their hostname, in the 'Proxy host' field. This field supports regular expressions, so you can create a profile to cover multiple slave servers, e.g. (PROXY3|PROXY5|PROXY8)

You can then use the profiles so created, in any of the filtering sections; like URL Filtering, Mime Filter, Keyword Filter, etc. for defining granular access policies.

Download: You can download SafeSquid free editions from here.

Also see: Other SafeSquid howtos 

Linux Commands For Shared Library Management & Debugging Problem

If you are a developer, you will re-use code provided by others. Usually /lib, /lib64, /usr/local/lib, and other directories stores various shared libraries. You can write your own program using these shared libraries. As a sys admin you need to manage and install these shared libraries. Use the following commands for shared libraries management, security, and debugging problems.

In Linux or UNIX like operating system, a library is noting but a collection of resources such as subroutines / functions, classes, values or type specifications. There are two types of libraries:

Static libraries - All lib*.a fills are included into executables that use their functions. For example you can run a sendmail binary in chrooted jail using statically liked libs.Dynamic libraries or linking [ also known as DSO (dynamic shared object)] - All lib*.so* files are not copied into executables. The executable will automatically load the libraries using ld.so or ld-linux.so.ldconfig : Updates the necessary links for the run time link bindings.ldd : Tells what libraries a given program needs to run.ltrace : A library call tracer.ld.so/ld-linux.so: Dynamic linker/loader.

As a sys admin you should be aware of important files related to shared libraries:

/lib/ld-linux.so.* : Execution time linker/loader./etc/ld.so.conf : File containing a list of colon, space, tab, newline, or comma separated directories in which to search for libraries. /etc/ld.so.cache : File containing an ordered list of libraries found in the directories specified in /etc/ld.so.conf. This file is not in human readable format, and is not intended to be edited. This file is created by ldconfig command.lib*.so.version : Shared libraries stores in /lib, /usr/lib, /usr/lib64, /lib64, /usr/local/lib directories.

You need to use the ldconfig command to create, update, and remove the necessary links and cache (for use by the run-time linker, ld.so) to the most recent shared libraries found in the directories specified on the command line, in the file /etc/ld.so.conf, and in the trusted directories (/usr/lib, /lib64 and /lib). The ldconfig command checks the header and file names of the libraries it encounters when determining which versions should have their links updated. This command also creates a file called /etc/ld.so.cache which used to speed linking.

In this example, you've installed a new set of shared libraries at /usr/local/lib/:
$ ls -l /usr/local/lib/
Sample outputs:

-rw-r--r-- 1 root root 878738 Jun 16 2010 libGeoIP.a-rwxr-xr-x 1 root root 799 Jun 16 2010 libGeoIP.lalrwxrwxrwx 1 root root 17 Jun 16 2010 libGeoIP.so -> libGeoIP.so.1.4.6lrwxrwxrwx 1 root root 17 Jun 16 2010 libGeoIP.so.1 -> libGeoIP.so.1.4.6-rwxr-xr-x 1 root root 322776 Jun 16 2010 libGeoIP.so.1.4.6-rw-r--r-- 1 root root 72172 Jun 16 2010 libGeoIPUpdate.a-rwxr-xr-x 1 root root 872 Jun 16 2010 libGeoIPUpdate.lalrwxrwxrwx 1 root root 23 Jun 16 2010 libGeoIPUpdate.so -> libGeoIPUpdate.so.0.0.0lrwxrwxrwx 1 root root 23 Jun 16 2010 libGeoIPUpdate.so.0 -> libGeoIPUpdate.so.0.0.0-rwxr-xr-x 1 root root 55003 Jun 16 2010 libGeoIPUpdate.so.0.0.0

Now when you run an app related to libGeoIP.so, you will get an error about missing library. You need to run ldconfig command manually to link libraries by passing them as command line arguments with the -l switch:
# ldconfig -l /path/to/lib/our.new.lib.so
Another recommended options for sys admin is to create a file called /etc/ld.so.conf.d/geoip.conf as follows:

/usr/local/lib

Now just run ldconfig to update the cache:
# ldconfig
To verify new libs or to look for a linked library, enter:
# ldconfig -v
# ldconfig -v | grep -i geoip
Sample outputs:

libGeoIP.so.1 -> libGeoIP.so.1.4.6libGeoIPUpdate.so.0 -> libGeoIPUpdate.so.0.0.0

You can print the current cache with the -p option:
# ldconfig -p
Putting web server such as Apache / Nginx / Lighttpd in a chroot jail minimizes the damage done by a potential break-in by isolating the web server to a small section of the filesystem. It is also necessary to copy all files required by Apache inside the filesystem rooted at /jail/ directory , including web server binaries, shared Libraries, modules, configuration files, and php/perl/html web pages. You need to also copy /etc/{ld.so.cache,ld.so.conf} files and /etc/ld.so.conf.d/ directory to /jail/etc/ directory. Use the ldconfig command to update, print and troubleshoot chrooted jail problems:

### chroot to jail bashchroot /jail /bin/bash### now update the cache in /jail ###ldconfig### print the cache in /jail ###ldconfig -p### copy missing libs ###cp /path/to/some.lib /jail/path/to/some.libldconfigldconfig -v | grep some.lib### get out of jail ###exit### may be delete bash and ldconfig to increase security (NOTE path carefully) ###cd /jailrm sbin/ldconfig bin/bash### now start nginx jail ###chroot /jail /usr/local/nginx/sbin/nginx 

A rootkit is a program (or combination of several programs) designed to take fundamental control of a computer system, without authorization by the system's owners and legitimate managers. Usually, rootkit use /lib, /lib64, /usr/local/lib directories to hide itself from real root users. You can use ldconfig command to view all the cache of all shared libraries and unwanted programs:
# /sbin/ldconfig -p | less
You can also use various tools to detect rootkits under Linux.

You may see the errors as follows:

Dynamic linker error in foo
Can't map cache file cache-file
Cache file cache-file foo

All of the above errors means the linker cache file /etc/ld.so.cache is corrupt or does not exists. To fix these errors simply run the ldconfig command as follows:
# ldconfig

The executable required a dynamically linked library that ld.so or ld-linux.so cannot find. It means a library called xyz needed by the program called foo not installed or path is not set. To fix this problem install xyz library and set path in /etc/ld.so.conf file or create a file in /etc/ld.so.conf.d/ directory.

ldd (List Dynamic Dependencies) is a Unix and Linux program to display the shared libraries required by each program. This tools is required to build and run various server programs in a chroot jail. A typical example is as follows to list the Apache server shared libraries, enter:
# ldd /usr/sbin/httpd
Sample outputs:

libm.so.6 => /lib64/libm.so.6 (0x00002aff52a0c000)libpcre.so.0 => /lib64/libpcre.so.0 (0x00002aff52c8f000)libselinux.so.1 => /lib64/libselinux.so.1 (0x00002aff52eab000)libaprutil-1.so.0 => /usr/lib64/libaprutil-1.so.0 (0x00002aff530c4000)libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002aff532de000)libldap-2.3.so.0 => /usr/lib64/libldap-2.3.so.0 (0x00002aff53516000)liblber-2.3.so.0 => /usr/lib64/liblber-2.3.so.0 (0x00002aff53751000)libdb-4.3.so => /lib64/libdb-4.3.so (0x00002aff5395f000)libexpat.so.0 => /lib64/libexpat.so.0 (0x00002aff53c55000)libapr-1.so.0 => /usr/lib64/libapr-1.so.0 (0x00002aff53e78000)libpthread.so.0 => /lib64/libpthread.so.0 (0x00002aff5409f000)libdl.so.2 => /lib64/libdl.so.2 (0x00002aff542ba000)libc.so.6 => /lib64/libc.so.6 (0x00002aff544bf000)libsepol.so.1 => /lib64/libsepol.so.1 (0x00002aff54816000)/lib64/ld-linux-x86-64.so.2 (0x00002aff527ef000)libuuid.so.1 => /lib64/libuuid.so.1 (0x00002aff54a5c000)libresolv.so.2 => /lib64/libresolv.so.2 (0x00002aff54c61000)libsasl2.so.2 => /usr/lib64/libsasl2.so.2 (0x00002aff54e76000)libssl.so.6 => /lib64/libssl.so.6 (0x00002aff5508f000)libcrypto.so.6 => /lib64/libcrypto.so.6 (0x00002aff552dc000)libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00002aff5562d000)libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00002aff5585c000)libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00002aff55af1000)libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00002aff55cf3000)libz.so.1 => /usr/lib64/libz.so.1 (0x00002aff55f19000)libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00002aff5612d000)libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00002aff56335000)

Now, you can copy all those libs one by one to /jail directory

# mkdir /jail/lib# cp /lib64/libm.so.6 /jail/lib# cp /lib64/libkeyutils.so.1 /jail/lib

You can write a bash script to automate the entire procedure:

cp_support_shared_libs(){ local d="$1" # JAIL ROOT local pFILE="$2" # copy bin file libs local files=""## use ldd to get shared libs list ### files="$(ldd $pFILE | awk '{ print $3 }' | sed '/^$/d')"  for i in $files do dcc="${i%/*}" # get dirname only [ ! -d ${d}${dcc} ] && mkdir -p ${d}${dcc} ${_cp} -f $i ${d}${dcc} done  # Works with 32 and 64 bit ld-linux sldl="$(ldd $pFILE | grep 'ld-linux' | awk '{ print $1}')" sldlsubdir="${sldl%/*}" [ ! -f ${d}${sldl} ] && ${_cp} -f ${sldl} ${d}${sldlsubdir}}

Call cp_support_shared_libs() it as follows:

cp_support_shared_libs "/jail" "/usr/local/nginx/sbin/nginx"

Type the following command:
$ ldd -d /path/to/executable

Type the following command:
$ ldd -r /path/to/executable

TCP Wrapper is a host-based Networking ACL system, used to filter network access to Internet. TCP wrappers was original written to monitor and stop cracking activities on the UNIX / Linux systems. To determine whether a given executable daemon supports TCP Wrapper or not, run the following command:
$ ldd /usr/sbin/sshd | grep libwrap
Sample outputs:

libwrap.so.0 => /lib64/libwrap.so.0 (0x00002abd70cbc000)

The output indicates that the OpenSSH (sshd) daemon supports TCP Wrapper.

You can use the ldd command when an executable is failing because of a missing dependency. Once you found a missing dependency, you can install it or update the cache with the ldconfig command as mentioned above.

The ltrace command simply runs the specified command until it exits. It intercepts and records the dynamic library calls which are called by the executed process and the signals which are received by that process. It can also intercept and print the system calls executed by the program. Its use is very similar to strace command.
# ltrace /usr/sbin/httpd
# ltrace /sbin/chroot /usr/sbin/httpd
# ltrace /bin/ls
Sample outputs:

__libc_start_main(0x804fae0, 1, 0xbfbd6544, 0x805bce0, 0x805bcd0 strrchr("/bin/ls", '/') = "/ls"setlocale(6, "") = "en_IN.utf8"bindtextdomain("coreutils", "/usr/share/locale") = "/usr/share/locale"textdomain("coreutils") = "coreutils"__cxa_atexit(0x8052d10, 0, 0, 0xbfbd6544, 0xbfbd6498) = 0isatty(1) = 1getenv("QUOTING_STYLE") = NULLgetenv("LS_BLOCK_SIZE") = NULLgetenv("BLOCK_SIZE") = NULLgetenv("BLOCKSIZE") = NULLgetenv("POSIXLY_CORRECT") = NULLgetenv("BLOCK_SIZE") = NULLgetenv("COLUMNS") = NULLioctl(1, 21523, 0xbfbd6470) = 0getenv("TABSIZE") = NULLgetopt_long(1, 0xbfbd6544, "abcdfghiklmnopqrstuvw:xABCDFGHI:"..., 0x0805ea40, -1) = -1__errno_location() = 0xb76b8694malloc(40) = 0x08c8e3e0memcpy(0x08c8e3e0, "", 40) = 0x08c8e3e0...............output truncatedfree(0x08c8e498) = free(NULL) = free(0x08c8e480) = exit(0 __fpending(0xb78334e0, 0xbfbd6334, 0xb78876a3, 0xb78968f8, 0) = 0fclose(0xb78334e0) = 0__fpending(0xb7833580, 0xbfbd6334, 0xb78876a3, 0xb78968f8, 0) = 0fclose(0xb7833580) = 0+++ exited (status 0) +++

The ltrace command is a perfect debugging utility in Linux:

To monitor the library calls used by a program and all the signals it receives. For tracking the execution of processes. It can also show system calls, used by a program.

Consider the following c program:

 #include int main(){printf("Hello world\n");return 0;} 

Compile and run it as follows:
$ cc hello.c -o hello
$ ./hello
Now use the ltrace command to tracking the execution of processes:
$ ltrace -S -tt ./hello
Sample outputs:

15:20:38.561616 SYS_brk(NULL) = 0x08f4200015:20:38.561845 SYS_access("/etc/ld.so.nohwcap", 00) = -215:20:38.562009 SYS_mmap2(0, 8192, 3, 34, -1) = 0xb770800015:20:38.562155 SYS_access("/etc/ld.so.preload", 04) = -215:20:38.562336 SYS_open("/etc/ld.so.cache", 0, 00) = 315:20:38.562502 SYS_fstat64(3, 0xbfaafe20, 0xb7726ff4, 0xb772787c, 3) = 015:20:38.562629 SYS_mmap2(0, 76469, 1, 2, 3) = 0xb76f500015:20:38.562755 SYS_close(3) = 015:20:38.564204 SYS_access("/etc/ld.so.nohwcap", 00) = -215:20:38.564372 SYS_open("/lib/tls/i686/cmov/libc.so.6", 0, 00) = 315:20:38.564561 SYS_read(3, "\177ELF\001\001\001", 512) = 51215:20:38.564694 SYS_fstat64(3, 0xbfaafe6c, 0xb7726ff4, 0xb7705796, 0x8048234) = 015:20:38.564822 SYS_mmap2(0, 0x1599a8, 5, 2050, 3) = 0xb759b00015:20:38.565076 SYS_mprotect(0xb76ee000, 4096, 0) = 015:20:38.565209 SYS_mmap2(0xb76ef000, 12288, 3, 2066, 3) = 0xb76ef00015:20:38.565454 SYS_mmap2(0xb76f2000, 10664, 3, 50, -1) = 0xb76f200015:20:38.565604 SYS_close(3) = 015:20:38.565709 SYS_mmap2(0, 4096, 3, 34, -1) = 0xb759a00015:20:38.565842 SYS_set_thread_area(0xbfab030c, 0xb7726ff4, 0xb759a6c0, 1, 0) = 015:20:38.566070 SYS_mprotect(0xb76ef000, 8192, 1) = 015:20:38.566185 SYS_mprotect(0x08049000, 4096, 1) = 015:20:38.566288 SYS_mprotect(0xb7726000, 4096, 1) = 015:20:38.566381 SYS_munmap(0xb76f5000, 76469) = 015:20:38.566522 __libc_start_main(0x80483e4, 1, 0xbfab04e4, 0x8048410, 0x8048400 15:20:38.566667 puts("Hello world" 15:20:38.566811 SYS_fstat64(1, 0xbfab0310, 0xb76f0ff4, 0xb76f14e0, 0x80484c0) = 015:20:38.566936 SYS_mmap2(0, 4096, 3, 34, -1) = 0xb770700015:20:38.567126 SYS_write(1, "Hello world\n", 12Hello world) = 1215:20:38.567282 <... puts resumed> ) = 1215:20:38.567348 SYS_exit_group(0 15:20:38.567454 +++ exited (status 0) +++

You need to carefully monitor the order and arguments of selected functions such as open() [used to open and possibly create a file or device] or chown() [used to change ownership of a file] so that you can spot simple kinds of race conditions or security related problems. This is quite useful for evaluating the security of binary programs to find out what kind of changes made to the system.

The ltrace command can be used to trace memory usage of the malloc() and free() functions in C program. You can calculate the amount of memory allocated as follows:
[node303 ~]$ ltrace -e malloc,free ./simulator arg1 agr2 arg3
The ltrace will start ./simulator program and it will trace the malloc() and free() functions. You can find out I/O problems as follows:
[node303 ~]$ ltrace -e fopen,fread,fwrite,fclose ./simulator arg1 agr2 arg3
You may need to change function names as your programming languages or UNIX platform may use different memory allocation functions.

The ld.so or / ld-linux.so used as follows by Linux:

To load the shared libraries needed by a program.To prepare the program to run, and then runs it.

Type the following command:
# cd /lib
For 64 bit systems:
# cd /lib64
Pass the --list option, enter:
# ./ld-2.5.so --list /path/to/executable

From the man page:

--verify verify that given object really is a dynamically linked object we can handle --library-path PATH use given PATH instead of content of the environment variable LD_LIBRARY_PATH --inhibit-rpath LIST ignore RUNPATH and RPATH information in object names in LIST

The LD_LIBRARY_PATH can be used to set a library path for finding dynamic libraries using LD_LIBRARY_PATH, in the standard colon seperated format:
$ export LD_LIBRARY_PATH=/opt/simulator/lib:/usr/local/lib
The LD_PRELOAD allow an extra library not specified in the executable to be loaded:
$ export LD_PRELOAD=/home/vivek/dirhard/libdiehard.so
Please note that these variables are ignored when executing setuid/setgid programs.

SQL Server 2005 Management Studio Express Edition

Microsoft Word - Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition.doc Managing SQL Server Express with SQL Server 2005 Management Studio Express Edition SQL Server Technical Article Writers: Eric Brown, Senior Consultant Quilogy Inc. Published: March 2006 Applies To: SQL Server 2005 Summary: Learn how to manage SQL Server 2005 Express Edition by using the free graphical management tool, SQL Server 2005 Management …

An Introduction to SQL Server Management Studio Express Edition SQL Server 2005 Management Studio Express Edition (SSMSE) provides the database developer and administrator with a robust set of tools for working with Microsoft® SQL Server™ Express Edition. Based on the same technology and functionality as that in SQL Server Management Studio, SSMSE uses Microsoft Visual Studio® .NET 2005 user interfaces and IDE layout, so that switching between SSMSE and Visual Studio .NET 2005 easy. This familiar interface allows .NET developers to be productive more quickly. The features in SSMSE are limited to those that are needed to manage a SQL Server 2005 Express Edition database. While you can use SSMSE to manage other editions of SQL Server 2005, you cannot manage components of the server that are not in SQL Server Express. For example, you can connect only to the relational database engine only by using SSMSE because other services are not installed with SQL Server Express. SSMSE and SQL Server Management Studio (SSMS) cannot be installed together. If multiple editions of SQL Server are installed on your computer, you can only use SSMS and not SSMSE. Getting started The next sections provide a tour of the features in SSMSE. Because of its graphical user interface, many SSMSE features can be accessed simply by right-clicking. You can accomplish many tasks by using both script and dialog boxes. The beginning developer can use dialog boxes to accomplish basic tasks. By design, SQL Server always creates a default state that is secure and functional. Changes to default settings are for the purpose of setting up the database for the unique needs of users and applications. Assuming that SQL Server 2005 Management Studio Express Edition is installed, let’s start by connecting to an instance. Connecting to servers This section covers how to connect to an instance of SQL Server Express. The Connect to Server dialog box allows users to provide both logon credentials and specific connection properties. You can use it to connect directly to SQL Server Express. For the authentication method, you can choose either SQL Server Authentication or Windows Authentication. By default, this is set to Windows Authentication. There are two tabs on the Connect to Server dialog box: the Login tab and the Connection Properties tab. To view the Connection Properties tab, click the Options button. Use the Connection Properties tab (shown in Figure 1) to input authentication credentials and server name….. 10 Microsoft Corporation ©2006 Figure 10: Specify Values for Template Parameters window Now, you have a basic understanding of how to create the basic objects that make up a database. In the next sections, we look at managing the database and server by using SSMSE. Basics of Database Administration Whether you are a beginning developer or a budding database administrator, you need to have some understanding of database administration. Following are the basic functions of database administration. • System management. Making sure the appropriate services and features are accessible and working. With SQL Server 2005, some features are turned off by default. Moreover, SQL Server Express is configured to not listen on the network via TCP/IP. • Security management. Managing access to the data. This starts at the operating system and ends with the database. This paper focuses on security topics related to users and logins. • Disaster preparedness. Recovering from user error and hardware and software failure. This paper covers backing up databases, understanding recovery models, and working with scripts to allow for the re-creation of database objects and data. In the following sections, we look at how to handle these database administration tasks by using SQL Server Browser, SQL Configuration Manager, and the SQL Surface Area Configuration tool. We review system management tasks, security settings, and backup and recovery models. System management When SQL Server Express is installed using the default settings, it is configured to be as secure as possible. Microsoft calls this configuration secure by default. After SQL Server Express is installed, you might need to reconfigure parts of the server based on your expected usage. For example, by default SQL Server Express allows only local connections. If you need to connect to SQL Server Express through your network, configure SQL Server Express to allow remote connections. A number of tools are available for accomplishing various configuration tasks. SQL Server Surface Area Configuration tool The Surface Area Configuration tool provides a scriptable interface for setting global security states for SQL Server features and services. Because SQL Server 2005 follows the security principle of secure by default, many features are turned off by default. Moreover, unless you indicate otherwise, the services you choose to install do not start automatically after installation is complete. Use the Surface Area Configuration tool to configure SQL Server and to manage SQL Server security. The Surface Area Configuration tool can be used to turn on and off services and features as well as to change configuration settings. It provides two basic sorting methods—View by Instance and View by Component. Use View by Component to quickly access and manage a certain grouping of features. View by Instance allows you to look over a specific instance of SQL Server and make changes

Getting to Know the SQL Server Management Studio

HOUR 3 Getting to Know the SQL Server Management Studio The Microsoft SQL Server Management Studio Express is the new interface that Microsoft has provided for management of your SQL … … Server database. It is the main tool that you will use when maintaining your databases and the objects they contain. In this hour you’ll learn:. The system databases that ship with SQL Server 2005 Express. What is available under the Security node of the Management Studio. The types of server objects that are available. What is available under …

Microsoft SQL Server Management StudioExpress 45 This not only provides you with standardization between databases, but provides you with a great jump start on creating the databases you need. If you modify Model, you do not affect any existing databases. All new databases will be affected by your changes. The MSDB Database The MSDB (Microsoft Database) databaseis used by SQL Server, SQL Server Management Studio, and SQL Server Agent. All three of them use it to store data, including scheduling information and backup and restore history information. For example, SQL Server maintains a complete backup and restore history in MSDB. There are several ways that you can add to or modify information stored in the MSDB database. They include . Scheduling tasks . Maintaining online backup and restore history . Replication The TempDB Database TempDB is asystem database that acts as a resource to all users working with a particular instance of SQL Server. TempDB holds the following objects: . Temporary user objects such as temporary tables, temporary stored procedures, temporary table variables, or cursors . Internal objects used by the database engine to perform tasks such as sorting . Row versions that are generated in data modification transactions The Security Node As its name implies, the Security Node enables you to manage SQL Server security. Using the Security Node, you can work with logins, add to and remove people from server roles, and create credentials. This chapter provides an introduction to security. For more information, see Hour 21, “SQL Server Authentication,” and Hour 22, “SQL Server Permissions Validation.” The Logins Node Logins representthe users and roles that have access to your system. Note in Figure 3.2 that two types of icons appear under the Logins node. One is granting a role access to the database, and the other is granting a user access to the database.
46 HOUR 3 Getting to Know the SQL Server Management Studio The Server Roles Node Server Roles arepredefined roles, supplied by SQL Server. Each Server Role possesses a pre-defined set of rights. Figure 3.3 shows the available Server Roles. You cannot add or remove Server Roles. FIGURE 3.2 Notice that Administrators is a role, and sa is a user. FIGURE 3.3 Each Server Role possesses a pre-defined set of rights.
Microsoft SQL Server Management StudioExpress 47 The Credentials Node Credentialsare new to SQL Server 2005. A credential is a record that contains the authentication information required for SQL Server to connect to an outside resource. Most credentials are made up of a Windows login and password. Server Objects Server Objects refer to a set of objects used at the server level (not at the database level). These objects include Backup Devices, Linked Servers, and Server Triggers. Backup Devices Backup devicesinclude the tapes and disks that you use to back up or restore your SQL Server. When creating a backup, you must designate the backup device that you want to use (see Figure 3.4). You select from a list of backup devices that you have created. FIGURE 3.4 When creating a backup, you must first designate the backup device you want to use. Linked Servers Linked serversenable you to work with other SQL Servers, as well as databases other than SQL Server databases, right from within Management Studio. This offers a few advantages: The Replication Node Data replication is the capability of a system to make copies of its data and application objects automatically in remote locations. You easily can propagateany changes to the original or data changes to the copies to all the other copies. Data replication enables users to make changes to data offline at remote locations. SQL Express synchronizes changes to either the original or the remote data with other instances of the database. The original database is referred to as the design master . You can make changes to definitions of tables or other application objects only at the design master. You use the design master to make special copies called replicas . Although there is only one design master, replicas can make other replicas. The process of the design master and replicas sharing changes is referred to as synchronization . To see an example of data replication at work, imagine that you have a team of salespeople who are out on the road all day. At the end of the day, each salesperson logs on to one of the company’s servers through Terminal Services. The replication process sends each salesperson’s transactions to the server. If necessary, the process sends any changes to the server data to the salesperson. Management The Management node contains tools that help you to manage your SQL Server. These tools include the capability to view both the SQL Server Logs and the Activity Monitor….. The Process Info page contains information about the connections. The Locks by Process page sorts the locks by the connection. The Locks by Object page sorts the locks by the object name. Creating a SQL Server Database Before you can build tables, views, stored procedures, triggers, functions, and other objects, you must create the database in which they will reside. A database is a collection of objects that relate to one another. An example would be all the tables and other objects necessary to build a sales order system. To create a SQL Server database, followthese steps: 1. Right-click the Databases node and select New Database. The New Database dialog appears (see Figure 3.5). 2. Enter a name for the database. 3. Enter a path for the database (see Figure 3.6). 4. Click to select the Options page and change any options as desired (see Figure3.7). 5. Click OK to close the New Database dialog and save the new database. The database now appears under the list of databases (see Figure 3.8) under the Databases node of SQL Server Management Studio. If the database does not appear, right-click the Databases node and select Refresh….