Tom Casey on Microsoft’s BI Strategy

Guys,

Let's look at the past, present, and future of BI through a discussion conducted by SQL Server Magazine. They talked with TOM CASEY, Microsoft general manager for SQL Server BI.

Click here to read the complete discussion.

Tom Casey on Microsoft’s BI Strategy

Enjoy

Happy SQL Coding

Authentication Modes in SQL Server

Hi,

Today we will discuss various authentication modes available in SQL Server. A script that helps to check current Authentication Mode and how we can change the Authentication mode from Registry.

There are 3 authentication modes in SQL Server:

• SQL Server
• Windows Authentication
• Mixed Mode

Let's discuss various modes in detail:

SQL Server Authentication allows a user to connect with a specified login name and password. when a request is received SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

The authentication diagram as per BOL is:

Windows Authentication mode allows a user to connect through a Microsoft Windows user account. When a user connects through a Windows user account, SQL Server validates the account name and password by calling back to Windows for the information. SQL Server achieves login security integration with Windows by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. name and password. Login security integration operates over any supported network protocol in SQL Server.

If a user attempts to connect to an instance of SQL Server providing a blank login name, SQL Server uses Windows Authentication. If a user attempts to connect to an instance of SQL Server configured for Windows Authentication Mode by using a specific login, the login is ignored and Windows Authentication is used.

Windows Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows security system. Windows security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

Because Windows users and groups are maintained only by Windows, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to an instance of SQL Server or logs on to Windows (depending on the type of change). Note that Windows Authentication Mode is not available when an instance of SQL Server is running on Windows.

Mixed Mode (Windows Authentication and SQL Server Authentication)
Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.

Windows vs SQLServer Authentication
Windows authentication is generally preferred because it provides an optimal level of integration with Windows 2000. User and group accounts from Windows are granted or denied access to SQL Server. Windows 2000 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user.

Windows Authentication provides following advantages over SQL Server Authentication.

• Windows Authentication can grant group accounts access SQL Server, thus minimizing the over head of login administration
• Users are authenticated by Windows, resulting in a secure authentication over a network
• Users could be able to use the same user credentials for network and database access
• Audited events can be tracked to a network user.

Change Authentication mode through Registry:

We can change the authentication mode from Windows Registry of the machine where SQL Server is installed using following steps:

Step 1: Let's check what is the current Authentication mode of our SQL Server:

Step 2: Click Start=>Run and type REGEDIT to open Registry Editor

Step 3: Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer. There you should be able to see LOGINMODE property.

Step 4: Double Click on LoginMode property, it will be either 1 or 2, change it.

Step 5: let's check what's the new/updated Authentication mode. Its already updated to SQL Server & Windows Authentication but it will not work till we restart the SQL Server Service.

Step 6: Restart SQL Server Service

Script to check Authentication Mode:

--SQL 2005 Version

set nocount on
go
DECLARE @SqlPath Nvarchar(255)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500) 
Declare @value_name Nvarchar(20)
Declare @LoginMode_Value int
Declare @LoginMode Nvarchar(15)

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name=@instance_name,
@value=@SqlPath output

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
END 

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='LoginMode',
@value=@LoginMode_Value output

if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed' 

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode

END

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'Software\Microsoft\MSSQLServer\MSSQLServer' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name=LoginMode,
@value=@LoginMode_Value output 


if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed' 

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END

Do let me know if you have any comments.

Regards,

HAPPY SQL CODING

Script to Rebuild Index on all Tables

Hi,

Today let's discuss how we can rebuild all indexes on a database.

Here is the script that will generate Rebuild Index statement for all tables & execute it.

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 
DECLARE @minId INT
DECLARE @maxId INT

SET @fillfactor = 90 

CREATE TABLE #tmp
(ID INT NOT NULL IDENTITY, Tabname VARCHAR(1000))


INSERT INTO #tmp(Tabname)
SELECT table_catalog + '.' + table_schema + '.[' + table_name + ']' as tableName FROM  INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'

SET @minId=1
SELECT @maxId=COUNT(1) FROM #tmp

WHILE @minId <= @maxId
BEGIN

SELECT @Table=tabname FROM #tmp WHERE id = @minId


SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  

PRINT @cmd
EXEC (@cmd)  

SET @minId = @minId + 1
END  


DROP TABLE #tmp

Let me know if you have any issue while executing the script.

Regards,

Happy SQL Coding

Future of BI (Singapore) on 12th March, 2010

Hi,

Singapore SQL Server Users Group and Singapore Windows IT Pro Users Group are inviting you to meet two prominent SQL Server General Managers from Redmond (HQ Product Group), in a never before conversation, hosted by Microsoft Singapore. Learn and influence how the future of Business Intelligence can be for you.


The Agenda/Discussion Topics include:

- Microsoft’s vision & journey for Business Intelligence

- Best Practices for Microsoft Business Intelligence

- SQL Server 2008 R2 and Beyond


Time and Venue details:

Date: 12 Mar 2010 - Time: 6.30pm – 8.00pm

Venue: 1 Marina Boulevard #21-00
Microsoft Auditorium


For registration, send email to  Juliet Soh at

Juliet@swap.com.sg / +65 9756 2504

with following information;

Name, Email, Contact, Company, Job Title


Let's see who are our Distinguished Speakers:

Tom Casey

General Manager, SQL Server Business Intelligence, Microsoft® SQL Server™
Microsoft Corporation


Billie Jo Murray
General Manager, SQL Central Services
Microsoft® SQL Server™

For more information, click here tn.Geeksengaged.com

Hope you will join for this informative session on 12th March.

Happy SQL Coding

Discussion on SQL 2008 R2 features

Hi,

Please refer to following article published by SQLMAG on SQL 2008 R2 features. In this article Mark Souza picks his favorite features of SQL 2008 R2.

You may read it here: Microsoft's Mark Souza Lists His Favorite SQL Server 2008 R2 Features

HAPPY SQL CODING