It’s very important to capture trends of the sizes of your SQL Server 2005 database because it allows you to plan for future space needs, notice types of problems, and plan for time periods of heavy volume. I’ll show you the simple method that I use to capture this information.
An exampleI will capture a snapshot of the information related to the sizes of my database files; in my next article, I will analyze the information to see when my data files and log files grow the most.
Each database on the SQL Server contains information regarding the size of the database files, along with some other related information. In order for me to get to this information, I need a method to retrieve the data from the individual databases one at a time. I have two available options:
sp_spaceused: This system stored procedure will return the size statistics for the current database context in which it is running. It is very useful for returning ad hoc information regarding database or table sizes within the database; however, it is not very friendly for reporting purposes. It is possible to capture the information for each database through a script, but it would require the use of a user-defined cursor.
sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself. This is the method I will use for my code to capture database file size information.
The information I want to gather and store is available in the sys.database_files system view. This gives me the size of the database files, along with some other handy information such as the state of the database, the manner in which the files grow (size or percentage), and if it is read-only. I will need to capture this information for each database.
The script below creates a table named DatabaseFiles (if it does not already exist) based upon the structure of the system view sys.database_files; it also adds a new column to capture when the record was added to the table.
IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
ENDNow it is time to populate the DatabaseFiles table. This script uses the sp_msforeachdb stored procedure and passes a SQL script that inserts data from the sys.database_files view into the DatabaseFiles table that I created above. If you examine the script, you will notice that I am building in the database name for each database. This is subtle, and it’s accomplished by the [?] prefix to the sys.database_files view. This code is actually executed in each database on the instance, and the name of the database is used in place of the [?] marker. Information for each database is inserted into the DatabaseFiles table with one line of code, and it is a lot easier than writing a cursor to do the same. I also added a GETDATE() call to indicate when the records were inserted into the table.
Note: This example somewhat goes against two coding standards that I am typically strict about: using SELECT * and inserting into a table without a column list. I omitted them because the SQL string that I am building would have been a lot less desirable to view. If this was code that I put into a production environment, I would have made the necessary changes accordingly.
EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'To make sure that all of my data was captured correctly, I’ll look at what is in the table.
SELECT * FROM DatabaseFiles
Thursday, February 21, 2008
Using the Computer Management Console’s Shared Folders snap-in
Managing open files, active shares, and user sessions can take up quite a bit of time. The Computer Management Console’s Shared Folders snap-in can make your job easier by showing remote activity and resource access on a given system.
Shared Folders will not list the documents that you are working on locally; keep this in mind if you open one of these objects on a system, and the view is empty. As with other Computer Management Console snap-ins such as Event Viewer, Shared Folders is available on all versions of Windows 2000, Windows XP, Windows Server 2003, and Windows Vista.
Components of the Shared Folders snap-in
Shared Folders includes the following three objects, which allow you to monitor systems from the comfort of your office for any system on your network.
Shares: Shows the active shares (including all administrative shares) for the system to which you are connected.
Sessions: Shows all the user sessions that are connected to your system. If someone is accessing a Windows Server 2003 resource remotely, this snap-in will show you their session. You can disconnect sessions by right-clicking a session and choosing either Disconnect Selected Session or Disconnect All Sessions.
Open Files: Shows the files on the system that are currently open and shows you which users have the files or folders open; this can be helpful in tracking down why other users cannot open certain files. When using Open Files, you can close any file that any user has open simply by right-clicking the file’s entry in the list and choosing Close Open File.
Remote connections
When accessing the Computer Management Console, you can connect remotely to other systems to view their resources. (The remote systems must be running Windows 2000 or higher.)
To connect remotely to other systems, follow these steps:
Open the Computer Management Console by right-clicking My Computer from the Windows XP Start menu. (In Windows 2000, you right-click My Computer from the desktop. In Windows Vista, you right-click Computer or enter Computer Management in the Start Menu’s Search box.)
Right-click the computer object at the top of the left pane and select Connect To Another Computer. Or, click the Action menu and select Connect To Another Computer.
Enter the name of the computer you wish to connect to and click OK.
If the desired system is available, the Computer Management Console will display the resources as available on the remote system.
Next week, I will focus on the Computer Management Console’s Local Users and Groups snap-in.
Shared Folders will not list the documents that you are working on locally; keep this in mind if you open one of these objects on a system, and the view is empty. As with other Computer Management Console snap-ins such as Event Viewer, Shared Folders is available on all versions of Windows 2000, Windows XP, Windows Server 2003, and Windows Vista.
Components of the Shared Folders snap-in
Shared Folders includes the following three objects, which allow you to monitor systems from the comfort of your office for any system on your network.
Shares: Shows the active shares (including all administrative shares) for the system to which you are connected.
Sessions: Shows all the user sessions that are connected to your system. If someone is accessing a Windows Server 2003 resource remotely, this snap-in will show you their session. You can disconnect sessions by right-clicking a session and choosing either Disconnect Selected Session or Disconnect All Sessions.
Open Files: Shows the files on the system that are currently open and shows you which users have the files or folders open; this can be helpful in tracking down why other users cannot open certain files. When using Open Files, you can close any file that any user has open simply by right-clicking the file’s entry in the list and choosing Close Open File.
Remote connections
When accessing the Computer Management Console, you can connect remotely to other systems to view their resources. (The remote systems must be running Windows 2000 or higher.)
To connect remotely to other systems, follow these steps:
Open the Computer Management Console by right-clicking My Computer from the Windows XP Start menu. (In Windows 2000, you right-click My Computer from the desktop. In Windows Vista, you right-click Computer or enter Computer Management in the Start Menu’s Search box.)
Right-click the computer object at the top of the left pane and select Connect To Another Computer. Or, click the Action menu and select Connect To Another Computer.
Enter the name of the computer you wish to connect to and click OK.
If the desired system is available, the Computer Management Console will display the resources as available on the remote system.
Next week, I will focus on the Computer Management Console’s Local Users and Groups snap-in.
Create your own special characters in Windows XP
If you’ve ever wanted to create your own font or maybe just a special character — for example, a character showing your initials for when you wish to approve documents with your “signature” — you can easily create your own special characters using a hidden Windows XP tool called the Private Character Editor. Here’s how:
Press [Windows]R to open the Run dialog box.
Type eudcedit in the Open text box and click OK.
When the Private Character Editor launches, you’ll see the Select Code dialog box. Click OK.
A user interface that looks and works very much like Paint will appear. From this, you may use standard tools to create your characters.
When you finish, select the Save Character command on the Edit menu.
Once you save your new character, you can access it using the Character Map tool. Here’s how:
Press [Windows]R to open the Run dialog box.
Type charmap in the Open text box and click OK.
When the Character Map appears, select the Font drop-down list and select All Fonts (Private Characters).
Select your character, click the Select button, and then click the Copy button.
You can now paste your font character in any document that you want.
Press [Windows]R to open the Run dialog box.
Type eudcedit in the Open text box and click OK.
When the Private Character Editor launches, you’ll see the Select Code dialog box. Click OK.
A user interface that looks and works very much like Paint will appear. From this, you may use standard tools to create your characters.
When you finish, select the Save Character command on the Edit menu.
Once you save your new character, you can access it using the Character Map tool. Here’s how:
Press [Windows]R to open the Run dialog box.
Type charmap in the Open text box and click OK.
When the Character Map appears, select the Font drop-down list and select All Fonts (Private Characters).
Select your character, click the Select button, and then click the Copy button.
You can now paste your font character in any document that you want.
Sunday, February 10, 2008
Enterprise considerations for Microsoft Network Access Protection
Having a MS-NAP implementation in place will provide your network an extra level of protection at the entry point. There are certainly networks that need the maximum level of security for every point of connectivity; however, only the business or your technology situation can determine what you need from the perspective of network access protection. The MS-NAP implementation uses many different communication mechanisms if fully implemented. A strong point for MS-NAP is that the MS-NAP implementation can be utilized with some or all of the features and roles. In this article, we'll take a look at some of things you need to take into consideration from an enterprise perspective.
Enforcement types for MS-NAP
If you are considering MS-NAP for your environment, you cannot invest enough time in the planning and testing phases. Deciding on the best enforcement type for a policy is critically important. The means of enforcing MS-NAP are varied in their functionality and complexity.
Enforcement types
The MS-NAP implementation can enforce the compliance policy through these four mechanisms:
VPN: The VPN server relays the policy from the Network Policy Server (NPS) to the requesting client and performs the validation. This is not to be confused with Windows Server 2003's Network Access Quarantine Control feature.
DHCP: The DHCP server interacts with the policies from the NPS to determine the client's compliance.
IPSec: The IPSec enforcement of MS-NAP is Microsoft's strongest offering for network access protection. It enforces the policy and configures the systems out of compliance with a limited access local IP security policy for remediation.
802.1X: The MS-NAP client authenticates over an 802.1X authenticated network and is the best solution when integrating hardware from other vendors. Luckily, the 802.1X authentication protocol was developed jointly by Microsoft, Cisco, HP, Trapeze, and Enterasys.
Each enforcement type will direct the client that is out of compliance to the remediation network where a resolution should be able to occur before accessing the desired network. The remediation network should be given some thorough planning. Making the remediation network a place where clients (managed or unmanaged) can gain the requisite updates or programs without support staff intervention will be critical in making the entire MS-NAP implementation a success. Choosing an enforcement method is an important first step in a successful implementation.
Planning what can happen on the remediation network is very important as well. Question whether updates be accessed from this network; if anti-virus updates/installations be accessed there; and, most importantly, whether the users perform the required updates automatically or without involving the client support staff.
Network Policy Server (NPS) mastery
In planning a MS-NAP implementation, a deep-level understanding of the NPS role of Windows Server 2008 should be reached. This server role will determine where systems will go based on their configuration. This is especially important because this server role touches other server roles or equipment depending on the enforcement mechanism selected. The NPS role also acts as a RADIUS server for the MS-NAP clients.
Real-world administration effort and support
Many network administrators are overworked and can have a difficult time perceiving a time where they could allocate the time to properly plan a network access protection system much less fully test and implement such a solution. The common response from a quick, unscientific survey of network administrators is "It would be nice, but I don't have the time" for a network access protection solution. Regardless of it being a Microsoft or a networking company solution, the responses are fairly consistent.
From an ongoing support perspective, the MS-NAP implementation can go one way or the other. If the remediation network has a way for the users to become compliant and a robust, intuitive way of doing such, the support effort will be minimized for ongoing access to networks from systems that have dipped out of compliance.
Networking hardware support
If the 802.1X enforcement method is selected, a unique challenge is presented. This method is unique because it would require maintaining support for the MS-NAP implementation from a networking hardware and server operating system perspective. While the implementations offered by the networking hardware vendors offer 802.1X authentication for an individual port, it takes an additional administration effort to ensure end-to-end compatibility.
New services on clients and domain group policy objects
For the client elements using the MS-NAP implementation, there are new services and local configuration elements that are required to utilize the functionality. Pushing these configuration elements to managed systems through an Active Directory domain GPO is the best way to deploy to large numbers of existing systems. The new configuration elements for the MS-NAP implementation are not available in Active Directory domains running at Windows Server 2003 level, but are available for Windows Server 2008 level domains. There are other ways to configure the new services for clients, but it would be optimal to be native in the domain group policy editor and link the new GPO to an OU or a domain.
It is not clear what implementation configuration would be required for Windows XP clients since Service Pack 3 is not yet available; nor is it clear how a Windows XP MS-NAP client would be managed -- if at all possible -- from a Windows Server 2008 functionality level Active Directory domain.
Enforcement types for MS-NAP
If you are considering MS-NAP for your environment, you cannot invest enough time in the planning and testing phases. Deciding on the best enforcement type for a policy is critically important. The means of enforcing MS-NAP are varied in their functionality and complexity.
Enforcement types
The MS-NAP implementation can enforce the compliance policy through these four mechanisms:
VPN: The VPN server relays the policy from the Network Policy Server (NPS) to the requesting client and performs the validation. This is not to be confused with Windows Server 2003's Network Access Quarantine Control feature.
DHCP: The DHCP server interacts with the policies from the NPS to determine the client's compliance.
IPSec: The IPSec enforcement of MS-NAP is Microsoft's strongest offering for network access protection. It enforces the policy and configures the systems out of compliance with a limited access local IP security policy for remediation.
802.1X: The MS-NAP client authenticates over an 802.1X authenticated network and is the best solution when integrating hardware from other vendors. Luckily, the 802.1X authentication protocol was developed jointly by Microsoft, Cisco, HP, Trapeze, and Enterasys.
Each enforcement type will direct the client that is out of compliance to the remediation network where a resolution should be able to occur before accessing the desired network. The remediation network should be given some thorough planning. Making the remediation network a place where clients (managed or unmanaged) can gain the requisite updates or programs without support staff intervention will be critical in making the entire MS-NAP implementation a success. Choosing an enforcement method is an important first step in a successful implementation.
Planning what can happen on the remediation network is very important as well. Question whether updates be accessed from this network; if anti-virus updates/installations be accessed there; and, most importantly, whether the users perform the required updates automatically or without involving the client support staff.
Network Policy Server (NPS) mastery
In planning a MS-NAP implementation, a deep-level understanding of the NPS role of Windows Server 2008 should be reached. This server role will determine where systems will go based on their configuration. This is especially important because this server role touches other server roles or equipment depending on the enforcement mechanism selected. The NPS role also acts as a RADIUS server for the MS-NAP clients.
Real-world administration effort and support
Many network administrators are overworked and can have a difficult time perceiving a time where they could allocate the time to properly plan a network access protection system much less fully test and implement such a solution. The common response from a quick, unscientific survey of network administrators is "It would be nice, but I don't have the time" for a network access protection solution. Regardless of it being a Microsoft or a networking company solution, the responses are fairly consistent.
From an ongoing support perspective, the MS-NAP implementation can go one way or the other. If the remediation network has a way for the users to become compliant and a robust, intuitive way of doing such, the support effort will be minimized for ongoing access to networks from systems that have dipped out of compliance.
Networking hardware support
If the 802.1X enforcement method is selected, a unique challenge is presented. This method is unique because it would require maintaining support for the MS-NAP implementation from a networking hardware and server operating system perspective. While the implementations offered by the networking hardware vendors offer 802.1X authentication for an individual port, it takes an additional administration effort to ensure end-to-end compatibility.
New services on clients and domain group policy objects
For the client elements using the MS-NAP implementation, there are new services and local configuration elements that are required to utilize the functionality. Pushing these configuration elements to managed systems through an Active Directory domain GPO is the best way to deploy to large numbers of existing systems. The new configuration elements for the MS-NAP implementation are not available in Active Directory domains running at Windows Server 2003 level, but are available for Windows Server 2008 level domains. There are other ways to configure the new services for clients, but it would be optimal to be native in the domain group policy editor and link the new GPO to an OU or a domain.
It is not clear what implementation configuration would be required for Windows XP clients since Service Pack 3 is not yet available; nor is it clear how a Windows XP MS-NAP client would be managed -- if at all possible -- from a Windows Server 2008 functionality level Active Directory domain.
Cisco's NAC hardware explained
Cisco Network Admission Control (NAC) is a system to enforce the security policy of your company on all devices attempting network access. The Cisco NAC solution is made up of many different pieces of hardware, software, and services; this article will explain its many pieces.
What hardware makes up Cisco's NAC solution?
On Cisco's network security solutions Web page, you'll find the following list of Cisco technologies, all of which play a part in the complete Cisco NAC solution:
Advanced Services for Network Security
Cisco Security Agent (CSA)
Cisco Security Monitoring, Analysis and Response System (MARS)
Cisco Trust Agent 2.0 (CTA)
Cisco Secure Access Control Server for Windows (ACS)
Cisco Secure Access Control Server Solution Engine (ACS)
Cisco Works Interface Configuration Manager (ICM)
Cisco Works Security Information Management Solution (CW-SIMS)
NAC-enabled routers
Router security
Cisco VPN 3000 Series Concentrators
Cisco Unified Wireless Network
Cisco Catalyst switches
Let's discuss some of the more critical pieces of Cisco's NAC solution.
Cisco NAC-enabled routers
The recently released Cisco router NAT module enforces NAC at the remote branch locations or ancillary buildings of a campus. Apart from that, the NAC router module also improves the overall security of the network by making sure that all incoming users and devices comply with security policies.
Additionally, the Cisco NAC router module (part # NME-NAC-K9) brings the capabilities of Cisco NAC Appliance Server to Cisco 2800 and 3800 Series Integrated Services Routers. This module helps network administrators by not having to deploy NAC appliances across the board and it helps to consolidate the administrative tasks into fewer boxes.
Amazingly, this module is actually a 1 GHz Intel Celeron PC, with 512 MB RAM, 64 MB of Compact Flash, and an 80 GB SATA hard drive. All that fits onto a single 1 pound module that slides into a router and enforces your security policies. This module requires a 2800 or 3800 series router running IOS 12.4(11)T or later.
Cisco NAC Appliance
The single most popular piece of the Cisco NAC solution has been the Cisco NAC Appliance. As evident from the name itself, Cisco NAC Appliance is an appliance-based solution that offers fast deployment, policy management, and enforcement of security policies.
With the Cisco NAC Appliance, you can opt for an in-band or out-of-band solution. The in-band solution is for smaller deployments. As your network grows into a more campus environment, you may not be able to keep in the in-band design. In that case, you can move to the out-of-band deployment scenario.
Here are some advantages of the Cisco NAC Appliance:
Identity: At the point of authentication, the Cisco NAC Appliance recognizes users, as well as their devices and their responsibility in the network.
Compliance: Cisco NAC Appliance also takes into account whether machines are compliant with security policies or not. This includes enforcing operating system updates, antivirus definitions, firewall settings, and antispyware software definitions.
Quarantine: If the machines attempting to gain access don't meet the policies of the network, the Cisco NAC Appliance can quarantine these machines and bring them into compliance (by applying patches or changing settings), before releasing them onto the network.
For more information about the Cisco NAC Appliance, see the Cisco NAC Appliance datasheet.
Cisco Secure Access Control Server (ACS)
The Cisco ACS Server could be called the "brain" of the Cisco NAC solution. It is here that users' credentials are checked to see if they are valid, policies are sent back to be enforced, and activities are logged. The ACS server is called an AAA Server because it performs authentication, authorization, and accounting.
This server runs on an existing Windows server in your organization and can use other existing databases in your organization to verify users' credentials. For example, most companies have ACS point toward their Windows Active Directory (AD) system to look up credentials. If those credentials are valid, then ACS can enforce network authorization polices on those users, with the help of the network hardware: NAC Appliance, Router NAC module, or ASA/PIX firewalls.
Cisco Security Agent (CSA)
Cisco CSA is a software client that is run on every machine in an organization. These clients talk to a centralized policy server. Together, these software applications know what software and activities that occur on each PC in the organization are or are not "normal". The CSA agent may alert on or block certain activities that it sees as abnormal.
When compared to anti-virus software that depends on definition updates to stay current, Cisco touts that the CSA never needs updating because it is constantly "learning" and monitoring activities, not definitions of viruses.
For more information about the Cisco CSA solution, see the Cisco CSA datasheet.
Cisco Trust Agent (CTA)
You can think of the Cisco Trust Agent as the "NAC Client". The CTA runs on each PC in the organization. It talks to the NAC Appliance, for example, to tell it about the state of the device attempting to access the network. For example, the CTA reports the version of the OS, patch level, the AV definition level, the firewall status, and more. According to Cisco, the CTA "interrogates devices." You can obtain CTA free of charge from Cisco Systems.
Cisco Works Security Information Management Solution (CW-SIMS)
The Cisco Works Security Information Management Solution (CW-SIMS) in the centralized repository that all Cisco devices use for security logging and other information. According to Cisco, this application "integrates, correlates, and analyzes security event data from the enterprise network to improve visibility and provide actionable intelligence for strengthening an organization's security."
With so many security devices in your network, one application has to try to correlate all the logs and security information that is generated. According to Cisco, here are the features that the CW-SIMS offers:
Comprehensive Correlation: Statistical, rules-based, and vulnerability correlation of events as they happen, in real time, across all integrated Cisco network devices.
Threat Visualization: See a visual status and generate reports of all the security events as they happen across your network.
Incident Resolution Management: SIMs integrates with common helpdesk packages to track security events until resolution.
Integrated Knowledge Base: SIMS can be a source of knowledge about security issues and how they are resolved.
Real-Time Notification: SIMS can notify security admins, in real time, when events occur.
For more information about the Cisco CW-SIMS solution, see the Cisco SW-SIMS datasheet.
Cisco Security Monitoring, Analysis, and Response System (MARS)
While MARS may seem similar to CW-SIMS, it is quite different. MARS actually understands the configuration and topology of your network. You can think of MARS as a "virtual security admin" for your network -- working while you sleep.
MARS uses NetFlow data from Cisco routers to have a real-time understanding of network traffic. It knows what is considered normal and what is not; this is called behavioral analysis. With behavioral analysis, MARS can stop abnormal network traffic. MARS has over 150 audit compliance templates ,and will make recommendations on how to remediate threats to your network.
MARS is actually an appliance that you install on your network. This appliance comes in a variety of sizes and license levels based on the size of your network. Cisco Security MARS and Cisco Security Manager are part of the Cisco Security Management Suite.
In summary
To be a complete solution that can fulfill the Cisco Self-Defending Network framework, the hardware and software of Cisco's NAC solution must integrate well. With nine or more different pieces of hardware and software related to NAC, the challenge of acquiring (i.e., affording), learning to configure, deploying, and monitoring these solutions can be a large task for any organization. While having the centralized software applications like CW-SIMS and MARS can really bring it all together, those applications will take time, effort, and expertise to master. For this reason, I can relate to anyone who says that deploying a security solution is difficult.
In this article, I've attempted to clarify the purpose of the different NAC security solutions offered by Cisco today; with this information, I hope that your quest for strong network security can be realized.
What hardware makes up Cisco's NAC solution?
On Cisco's network security solutions Web page, you'll find the following list of Cisco technologies, all of which play a part in the complete Cisco NAC solution:
Advanced Services for Network Security
Cisco Security Agent (CSA)
Cisco Security Monitoring, Analysis and Response System (MARS)
Cisco Trust Agent 2.0 (CTA)
Cisco Secure Access Control Server for Windows (ACS)
Cisco Secure Access Control Server Solution Engine (ACS)
Cisco Works Interface Configuration Manager (ICM)
Cisco Works Security Information Management Solution (CW-SIMS)
NAC-enabled routers
Router security
Cisco VPN 3000 Series Concentrators
Cisco Unified Wireless Network
Cisco Catalyst switches
Let's discuss some of the more critical pieces of Cisco's NAC solution.
Cisco NAC-enabled routers
The recently released Cisco router NAT module enforces NAC at the remote branch locations or ancillary buildings of a campus. Apart from that, the NAC router module also improves the overall security of the network by making sure that all incoming users and devices comply with security policies.
Additionally, the Cisco NAC router module (part # NME-NAC-K9) brings the capabilities of Cisco NAC Appliance Server to Cisco 2800 and 3800 Series Integrated Services Routers. This module helps network administrators by not having to deploy NAC appliances across the board and it helps to consolidate the administrative tasks into fewer boxes.
Amazingly, this module is actually a 1 GHz Intel Celeron PC, with 512 MB RAM, 64 MB of Compact Flash, and an 80 GB SATA hard drive. All that fits onto a single 1 pound module that slides into a router and enforces your security policies. This module requires a 2800 or 3800 series router running IOS 12.4(11)T or later.
Cisco NAC Appliance
The single most popular piece of the Cisco NAC solution has been the Cisco NAC Appliance. As evident from the name itself, Cisco NAC Appliance is an appliance-based solution that offers fast deployment, policy management, and enforcement of security policies.
With the Cisco NAC Appliance, you can opt for an in-band or out-of-band solution. The in-band solution is for smaller deployments. As your network grows into a more campus environment, you may not be able to keep in the in-band design. In that case, you can move to the out-of-band deployment scenario.
Here are some advantages of the Cisco NAC Appliance:
Identity: At the point of authentication, the Cisco NAC Appliance recognizes users, as well as their devices and their responsibility in the network.
Compliance: Cisco NAC Appliance also takes into account whether machines are compliant with security policies or not. This includes enforcing operating system updates, antivirus definitions, firewall settings, and antispyware software definitions.
Quarantine: If the machines attempting to gain access don't meet the policies of the network, the Cisco NAC Appliance can quarantine these machines and bring them into compliance (by applying patches or changing settings), before releasing them onto the network.
For more information about the Cisco NAC Appliance, see the Cisco NAC Appliance datasheet.
Cisco Secure Access Control Server (ACS)
The Cisco ACS Server could be called the "brain" of the Cisco NAC solution. It is here that users' credentials are checked to see if they are valid, policies are sent back to be enforced, and activities are logged. The ACS server is called an AAA Server because it performs authentication, authorization, and accounting.
This server runs on an existing Windows server in your organization and can use other existing databases in your organization to verify users' credentials. For example, most companies have ACS point toward their Windows Active Directory (AD) system to look up credentials. If those credentials are valid, then ACS can enforce network authorization polices on those users, with the help of the network hardware: NAC Appliance, Router NAC module, or ASA/PIX firewalls.
Cisco Security Agent (CSA)
Cisco CSA is a software client that is run on every machine in an organization. These clients talk to a centralized policy server. Together, these software applications know what software and activities that occur on each PC in the organization are or are not "normal". The CSA agent may alert on or block certain activities that it sees as abnormal.
When compared to anti-virus software that depends on definition updates to stay current, Cisco touts that the CSA never needs updating because it is constantly "learning" and monitoring activities, not definitions of viruses.
For more information about the Cisco CSA solution, see the Cisco CSA datasheet.
Cisco Trust Agent (CTA)
You can think of the Cisco Trust Agent as the "NAC Client". The CTA runs on each PC in the organization. It talks to the NAC Appliance, for example, to tell it about the state of the device attempting to access the network. For example, the CTA reports the version of the OS, patch level, the AV definition level, the firewall status, and more. According to Cisco, the CTA "interrogates devices." You can obtain CTA free of charge from Cisco Systems.
Cisco Works Security Information Management Solution (CW-SIMS)
The Cisco Works Security Information Management Solution (CW-SIMS) in the centralized repository that all Cisco devices use for security logging and other information. According to Cisco, this application "integrates, correlates, and analyzes security event data from the enterprise network to improve visibility and provide actionable intelligence for strengthening an organization's security."
With so many security devices in your network, one application has to try to correlate all the logs and security information that is generated. According to Cisco, here are the features that the CW-SIMS offers:
Comprehensive Correlation: Statistical, rules-based, and vulnerability correlation of events as they happen, in real time, across all integrated Cisco network devices.
Threat Visualization: See a visual status and generate reports of all the security events as they happen across your network.
Incident Resolution Management: SIMs integrates with common helpdesk packages to track security events until resolution.
Integrated Knowledge Base: SIMS can be a source of knowledge about security issues and how they are resolved.
Real-Time Notification: SIMS can notify security admins, in real time, when events occur.
For more information about the Cisco CW-SIMS solution, see the Cisco SW-SIMS datasheet.
Cisco Security Monitoring, Analysis, and Response System (MARS)
While MARS may seem similar to CW-SIMS, it is quite different. MARS actually understands the configuration and topology of your network. You can think of MARS as a "virtual security admin" for your network -- working while you sleep.
MARS uses NetFlow data from Cisco routers to have a real-time understanding of network traffic. It knows what is considered normal and what is not; this is called behavioral analysis. With behavioral analysis, MARS can stop abnormal network traffic. MARS has over 150 audit compliance templates ,and will make recommendations on how to remediate threats to your network.
MARS is actually an appliance that you install on your network. This appliance comes in a variety of sizes and license levels based on the size of your network. Cisco Security MARS and Cisco Security Manager are part of the Cisco Security Management Suite.
In summary
To be a complete solution that can fulfill the Cisco Self-Defending Network framework, the hardware and software of Cisco's NAC solution must integrate well. With nine or more different pieces of hardware and software related to NAC, the challenge of acquiring (i.e., affording), learning to configure, deploying, and monitoring these solutions can be a large task for any organization. While having the centralized software applications like CW-SIMS and MARS can really bring it all together, those applications will take time, effort, and expertise to master. For this reason, I can relate to anyone who says that deploying a security solution is difficult.
In this article, I've attempted to clarify the purpose of the different NAC security solutions offered by Cisco today; with this information, I hope that your quest for strong network security can be realized.
Finding dependencies in SQL Server 2005
Any time you need to modify objects in your SQL Server 2005 database, the objects that are dependent upon those objects are a concern. You don’t want to remove columns from tables, procedures, views, or tables if there are objects dependent upon them that are being used.
This tutorial will show how you can write a procedure that will look up all of the objects that are dependent upon other objects.
How to write the procedureTo start a dependency chain, I create a table and then create some objects that will depend upon that table. Below is a script to create my SalesHistory and load some data into it:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [char](150) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
ENDI’ll create a couple of objects that are dependent upon the SalesHistory table. This view uses the DENSE_RANK ranking function to return the sales rank of each product based on when the product was entered into the table. This view is directly dependent upon the SalesHistory table.
CREATE VIEW vw_SalesHistory
AS
SELECT SaleRank = DENSE_RANK() OVER (PARTITION BY Product ORDER BY SaleID ASC), *
FROM SalesHistory
GOThe stored procedure returns the total sales for the Computer product group. This procedure uses the view that I just created, so it is dependent upon that view, which is dependent upon the SalesHistory table. In a sense, this creates a dependency chain.
CREATE PROCEDURE usp_GetTotalComputerSales
(
@TotalSales MONEY OUTPUT
)
AS
BEGIN
SELECT @TotalSales = SUM(SalePrice)
FROM vw_SalesHistory
WHERE Product = 'Computer'
END
GOHere is the code to create the system stored procedure for finding object dependencies:
USE master
GO
CREATE PROCEDURE sp_FindDependencies
(
@ObjectName SYSNAME,
@ObjectType VARCHAR(5) = NULL
)
AS
BEGIN
DECLARE @ObjectID AS BIGINT
SELECT TOP(1) @ObjectID = object_id
FROM sys.objects
WHERE name = @ObjectName
AND type = ISNULL(@ObjectType, type)
SET NOCOUNT ON ;
WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
AS
(
SELECT DISTINCT
sd.object_id,
OBJECT_NAME(sd.object_id),
ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
ReferencedObjectID = sd.referenced_major_id
FROM
sys.sql_dependencies sd
JOIN sys.objects so ON sd.referenced_major_id = so.object_id
WHERE
sd.referenced_major_id = @ObjectID
UNION ALL
SELECT
sd.object_id,
OBJECT_NAME(sd.object_id),
OBJECT_NAME(referenced_major_id),
object_id
FROM
sys.sql_dependencies sd
JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID
WHERE
sd.referenced_major_id <> sd.object_id
)
SELECT DISTINCT
DependentObjectName
FROM
DependentObjectCTE c
ENDThis procedure uses a Common Table Expression (CTE) with recursion to walk down the dependency chain to get to all of the objects that are dependent on the object passed into the procedure. The main source of data comes from the system view sys.sql_dependencies, which contains dependency information for all of your objects in the database.
Note: There are exceptions to this table. SQL Server 2005 will only place data into the sys.sql_dependencies view if it is able to at the creation of the object. If the database is not able to add a dependency, it will let you know at the time the object is created.
I want to mark the stored procedure as a system stored procedure so I can call it for any object in any database.
EXECUTE sp_ms_marksystemobject 'sp_FindDependencies'Now I can call my new system stored procedure to find any objects that are dependent upon the SalesHistory table that I just created.
EXECUTE sp_FindDependencies 'SalesHistory'I get the results that I expect from the procedure. The following objects are returned:
usp_GetTotalComputerSales
vw_SalesHistoryThe view vw_SalesHistory is returned because it is directly dependent upon the SalesHistory table. The procedure usp_GetTotalComputerSales is returned because it is dependent upon the view vw_SalesHistory, which in turn is dependent upon the SalesHistory table.
Use with cautionThe ability to view objects that are dependent upon other objects (e.g., views that use tables, procedures that use views) is useful when you need to alter or remove certain objects. Be extra careful when you modify objects that other objects may depend on.
This tutorial will show how you can write a procedure that will look up all of the objects that are dependent upon other objects.
How to write the procedureTo start a dependency chain, I create a table and then create some objects that will depend upon that table. Below is a script to create my SalesHistory and load some data into it:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [char](150) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
VALUES
('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
ENDI’ll create a couple of objects that are dependent upon the SalesHistory table. This view uses the DENSE_RANK ranking function to return the sales rank of each product based on when the product was entered into the table. This view is directly dependent upon the SalesHistory table.
CREATE VIEW vw_SalesHistory
AS
SELECT SaleRank = DENSE_RANK() OVER (PARTITION BY Product ORDER BY SaleID ASC), *
FROM SalesHistory
GOThe stored procedure returns the total sales for the Computer product group. This procedure uses the view that I just created, so it is dependent upon that view, which is dependent upon the SalesHistory table. In a sense, this creates a dependency chain.
CREATE PROCEDURE usp_GetTotalComputerSales
(
@TotalSales MONEY OUTPUT
)
AS
BEGIN
SELECT @TotalSales = SUM(SalePrice)
FROM vw_SalesHistory
WHERE Product = 'Computer'
END
GOHere is the code to create the system stored procedure for finding object dependencies:
USE master
GO
CREATE PROCEDURE sp_FindDependencies
(
@ObjectName SYSNAME,
@ObjectType VARCHAR(5) = NULL
)
AS
BEGIN
DECLARE @ObjectID AS BIGINT
SELECT TOP(1) @ObjectID = object_id
FROM sys.objects
WHERE name = @ObjectName
AND type = ISNULL(@ObjectType, type)
SET NOCOUNT ON ;
WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
AS
(
SELECT DISTINCT
sd.object_id,
OBJECT_NAME(sd.object_id),
ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
ReferencedObjectID = sd.referenced_major_id
FROM
sys.sql_dependencies sd
JOIN sys.objects so ON sd.referenced_major_id = so.object_id
WHERE
sd.referenced_major_id = @ObjectID
UNION ALL
SELECT
sd.object_id,
OBJECT_NAME(sd.object_id),
OBJECT_NAME(referenced_major_id),
object_id
FROM
sys.sql_dependencies sd
JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID
WHERE
sd.referenced_major_id <> sd.object_id
)
SELECT DISTINCT
DependentObjectName
FROM
DependentObjectCTE c
ENDThis procedure uses a Common Table Expression (CTE) with recursion to walk down the dependency chain to get to all of the objects that are dependent on the object passed into the procedure. The main source of data comes from the system view sys.sql_dependencies, which contains dependency information for all of your objects in the database.
Note: There are exceptions to this table. SQL Server 2005 will only place data into the sys.sql_dependencies view if it is able to at the creation of the object. If the database is not able to add a dependency, it will let you know at the time the object is created.
I want to mark the stored procedure as a system stored procedure so I can call it for any object in any database.
EXECUTE sp_ms_marksystemobject 'sp_FindDependencies'Now I can call my new system stored procedure to find any objects that are dependent upon the SalesHistory table that I just created.
EXECUTE sp_FindDependencies 'SalesHistory'I get the results that I expect from the procedure. The following objects are returned:
usp_GetTotalComputerSales
vw_SalesHistoryThe view vw_SalesHistory is returned because it is directly dependent upon the SalesHistory table. The procedure usp_GetTotalComputerSales is returned because it is dependent upon the view vw_SalesHistory, which in turn is dependent upon the SalesHistory table.
Use with cautionThe ability to view objects that are dependent upon other objects (e.g., views that use tables, procedures that use views) is useful when you need to alter or remove certain objects. Be extra careful when you modify objects that other objects may depend on.
Subscribe to:
Posts (Atom)