Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Managing SQL Server 2005 Security

By Exforsys | on December 24, 2005 |
SQL Server 2005

Managing SQL Server 2005 Security

In this tutorial you will learn about Managing SQL Server 2005 Security. Microsoft has consciously invested in the creation of a precise and flexible security model of the database platform with security features that include surface area reduction, data encryption, native encryption, authentication and granular permissions and user and schema separations. This is part of the Trustworthy Computing initiative.

SQL Server 2005 Security Overview

The Trustworthy Computing initiative provides a framework for secure computing. Confidentiality, integrity and availability of data and systems are the cornerstones of the software life cycle. The Trustworthy computing initiative insists that the application should be secure by design. Therefore, Microsoft have built in multiple security audits and a threat analysis has been performed to evaluate every issue and additional design and testing work was carried out to neutralize potential risks. The second feature of the initiative is that the software has to be secure by default. Therefore, many of the features are disabled by default and users have to configure them if required. The third aspect that Microsoft focused on was that the SQL Server has to be secure in deployment. Proper security credentials and permissions have to be set. The content and the deployment tools provide the users with the information required for the decisions required to be taken during deployment. Security updates are also easy to find and install. Trustworthy computing initiative also includes use of views to access system tables, adaptable enforcement of password policies and improved database encryption capabilities. These features are further supported by Microsoft’s communication strategy that enables users of SQL Server to be intimated by Microsoft of any security threats that they face and the action they need to take and what tools to use to counter such threats.

Managing SQL Server 2005 Security

SQL Server 2005 supports Windows and mixed authentication modes and is closely integrated with it. In this mode access is granted based on a security token assigned during successful domain logon by a Windows account and the SQL Server is requested access subsequently. The precondition is that both must belong to the same windows environment. The Active Directory domain environment provides an additional level of protection of the Kerberos protocol. This protocol governs the behaviour of the Windows authentication mechanism. In the mixed mode SQL Server Authentication can also be used. The credentials are verified from the repository maintained by the SQL Server. The increased security has made redundant the need to maintain separate set of accounts. However, the SQL Server logins have been improved with encryption of SQL Generated Certificates for communications that involve MADC client software based on .NET provider.

A very significant enhancement to SQL Server 2005 is the ability to manage account passwords and lockout properties. This can be within the local and domain based group policies. The DBA can impose restrictions on password complexity, password expiration and account lockout. The following complexities can be imposed:

  1. The length of the password can be set to be minimum 6 characters.
  2. The password can contain uppercase characters, lowercase character, numbers and non-alphanumeric characters.
  3. The password cannot be “Admin”, “Administrator”, “Password” etc

The Password expiration can be determined by the values of “Maximum password age” and the lockout behaviour can be determined by “Account lockout duration”, “Account lockout threshold”, “Reset account lockout counter after”. ALTER LOGIN T-SQL statement can be used to unlock locked password.

The DBA uses the CHECK_EXPIRATION and CHECK_POLICY clauses while creating new logins with the CREATE LOGIN T-SQL statement. While CHECK_EXPIRATION controls the password expiration, CHECK_POLICY controls account lockout settings. Both have to be set ON or OFF. Other combinations are not supported. The syntax would be as under:

CREATE LOGIN xxx
WITH
PASSWORD = ‘CHANGEPASS’ MUST_CHANGE,
CHECK_EXPIRATION = ON, CHECK_POLICY = ON

The enforcement of the password policy for the existing logins can be verified by the DBA from the catalog view outputs. This can be verified in the graphical user interface of SQL Server Management Studio.

The endpoints in SQL Server 2005 are versatile with different transport and payload protocols, listening ports, authentication modes and permissions. When creating or modifying HTTP endpoints using the CREATE ENDPOINT and ALTER ENDPOINT statements the preferred login type is designated by the LOGIN_TYPE option(which can be WINDOWS or MIXED values). While WINDOWS is default, the MIXED mode will have to be configured to operate over a Secure Socket Layer channel. The login credentials must be specified in the Web Services Security headers preceding the SOAP requests of the client application.

The HTTP authentication mechanism can be assigned an Integrated, Digest or Basic value if the communication is SOAP based. The INTEGRATED mechanism applies windows based Kerberos or NTLM authentication protocol when establishing the HTTP communication between the client and server. The SQL Server account must be associated with Service Principal Name for the mutual Kerberos authentication to work. DIGEST is a hashing algorithm applied to user’s windows credentials on the client side. This is compared with the result of the same algorithm being applied on the server side.

BASIC compares the Windows BASE 64 Credentials on the client and server side.

« « SQL Server 2005 – Using the Database Tuning Advisor
SQL Server 2005 – Managing Permissions » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • SQL Server Monitoring Tools – Server Profiler

    December 12, 2005 - 0 Comment
  • Using Notification Services in SQL Server 2005

    October 25, 2005 - 0 Comment
  • Maintain indexes in a SQL Server 2005 database

    December 20, 2005 - 0 Comment
  • SQL Server 2005 Architecture Overview

    November 23, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Using DDL Triggers

    December 12, 2005 - 0 Comment
  • Using XML in SQL Server 2005

    October 26, 2005 - 0 Comment
  • SQL Server 2005 – Tuning a Database

    December 24, 2005 - 0 Comment
  • SQL server 2005 Editions

    November 23, 2005 - 0 Comment
  • SQL Server 2005 – Using Event Notifications

    December 13, 2005 - 0 Comment
  • SQL Server 2005 – Configuring Replication

    January 11, 2006 - 0 Comment
  • SQL Server 2005 Replication Enhancements

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Mirror Server

    January 11, 2006 - 0 Comment
  • SQL Server 2005 – Introduction to Data Availability

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Backing up a Database

    January 1, 2006 - 0 Comment
  • SQL Server 2005 – Using Database Snapshots

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Disaster Recovery

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Certificates

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Managing Permissions

    December 26, 2005 - 0 Comment
  • SQL Server 2005 – Using the Database Tuning Advisor

    December 24, 2005 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • SQL Server 2005 – Configuring Replication
  • SQL Server 2005 Replication Enhancements
  • SQL Server 2005 – Mirror Server
  • SQL Server 2005 – Introduction to Data Availability
  • SQL Server 2005 – Backing up a Database

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT