Understanding Authentication Protocols

Authentication - It is a process of allowing or denying user access to network.

Network Authentication Protocols
NTLM  - NT Lan Manager
Kerberos

The above two protocols are commonly used in networks to authenticate users against the domain controller.

NT Lan Manager -- Is the authentication mechanism introduced in Windows NT. It is higher version of LM (Lan Manager), It uses a challenge response algorithm.
Whenever client request access to server or any resource then client sends username to server, the server generates and sends a challenge to the client;
then client encrypts that challenge using the user's password and sends response to the server. If the user is a local server then server validates response against
Security Account Manager, if the user is a domain user then server forward response to domain controller to validate and retrieve group policy of that user account,
and construct access token and establish session. In this methodology everytime client needs to validate to access network resources hence there will be load.

Kerberos
There are three heads of kerberos KDC (key distribution center), AS (Authentication Service) and TGS (Ticket Granting System).  The KDC installed as a part of domain controller.
It uses a ticket based approach. When a client computer requests access to a server or resources on it requests a TGT(Ticket Grant Ticket) from the AS in users domain.
While in process of authentication a ticket is issued to client from domain controller, once it received the ticket then client can get access the resources (if TGT is presents on the server)
on the network with out having authenticated again or we can call it as single-sign-on. It is a time bases the tickets can expire certain time. In order to use kerberos both the client and server must be on same domain. It requires registered SPN (Service Principal Name) are unique identifiers for services running on servers.

Login prompt multiple times and getting blank screen in SQL 2008 reporting services

Recently I faced the below issue

I got login prompt multiple times while opening SSRS page of another server and eventually getting blank screen.

workaround:

Ensure RsWindowsNTLM is specified in the file rsreportserver.config instead of RsWindowsNegotiate.

(or)

Change the report service account to network service from SSRS configuration and try from other system to login into reporting services.

Query to find number of rows in each table

select
 so.name as Table_Name,
 si.rows as Row_count
 from sysobjects as so
 inner join sysindexes as si
 on so.id = si.id
 where so.xtype = 'u'
 and si.indid < 2
 order by so.name



SELECT t.NAME AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND i.OBJECT_ID > 255 AND i.index_id <= 1 GROUP BY t.NAME, i.object_id, i.index_id, i.name ORDER BY object_name(i.object_id)

How to clear recent servers list from SQL Server drop down list

In SQL Server 2005

1. Close SSMS
2. Type "%APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\ at Run
3. Browse for MRU (Most Recent Users).dat and delete it.
4. Open SSMS



In SQL Server 2008

1.Close SSMS
2.Type "%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\ at Run
3.In SQL 2008 mru.dat was renamed as Sqlstudio.bin, browse for that and delete
4. Open SSMS
 

Useful Commands for SQL Server Cluster

Below command is to view that SQL Server is a clustered or standalone

WITH prop_cte (cluster_prop)
AS
(SELECT SERVERPROPERTY('Isclustered') as cluster_prop)
SELECT CASE WHEN cluster_prop = 0 THEN 'Stand Alone' ELSE 'Clustered' end FROM prop_cte

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')  -- This displays on which cluster node SQL Server is running.

Select * from sys.dm_os_cluster_nodes  -- Displays nodes of the current SQL Server

 select * from sys.dm_io_cluster_shared_drives -- Displays cluster shared drives information from
   t-sql

How to change server collation in sql server ?

Steps

1. Backup all system databases
2. Script all logins and server roles
3. Copy SQL Server binaries into some folder.

Run the below command from command prompt.

4. Start /wait D:\MicrosoftSQL2005EE\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abcd1234 SQLCOLLATION=Latin1_General_CI_AS (New Collation Name)

 

qb - silent installation.

Instance Name - Name of the SQL Instance for which you want to update the collation.

RebuildDatabase - Hence we are rebuilding sql server databses with new collation settings.

SQLCollation -- Specify new collation name.

SAPWD -- SA password for server after rebuild .

 

It displays setup wizard, Click Yes to overwrite system databases and proceed.

After completion of setup wizard.

 

1. Open SSMS using new sa password

2. Attach all user databases

3. Execute the logins script

4. Execute the server roles scripts

 

Note -- This will rebuild all system databases.

 

 

 

 

can we drop multiple tables /objects without using dynamic sql

Can we drop multiple objects without using dynamic sql ?

Yes, It is possible. We can directly pass multiple table names using comma seperated.

drop table tabl1, tabl2, tabl3 and so on.

To do multiple inserts

Insert into tabl1 values (val1, val2), (val1, val2), (val1, val2)  and so on.

 

Shrink log for all databases

Below query helps to shrink transaction log for all databases

SELECT
      'USE [' + sd.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + smf.name + N''' )'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
    sys.master_files smf
    JOIN sys.databases sd
        ON smf.database_id = sd.database_id WHERE smf.file_id=2
and sd.database_id > 4;
 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

We may encounter the below error while setup linked server from SQL Server to Oracle
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server Oracle

Steps to troubleshoot

1. Verify the DNS using ODBC from Administrative tools by using Oracle Client Driver.
2. If DNS test connection is succeds then we need to setup linked server from SQL Server
3. SSMS - Server Objects - Linked Servers  -- New Linked Server
4. Linked Server -- OracleLinkServer
5. Select Other Data Source
6. Provider -- Select Microsoft OLEDB Provider for ODBC Drivers
7. Product Name -- Specify Oracle
8. Data Source -- Specify DSN which we created earlier
9. On Security (at left pane) Select Be made using the security context
10. Specify Oracle user name and password
11.On Server Options, make sure Data Access, RPC and RPC Out as True.
12. Finally hit OK

Hope that will fix the issue if oracle client components are installed on the machine.

To Query Linked Server

Select * from OpenQuery(linkedservername, 'query')

Select * from openquery(oraclelinkserver, 'select * from test')

Insert into openquery (oraclelinkserver,'select name, designation from test') values 'kalyan', 'dba'

Insert into openquery(oraclelinkeserver, 'select name, designation from test') select name, designation from test (sql server table)

delete from openquery(oraclelinkserver, 'delete from test where name =''kalyan''');

update openquery(oraclelinkserver,'select name from test where id=1') set name='kalyan kumar';

 

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON Microsoft SQL Server, Error: 15128

We normally faced this issue when SQL Login is set to "Enforce password policy” and “Enforce password expiration”, once set this policy and expiration can’t be changed using the below commands.

ALTER LOGIN kalyanlogin WITH CHECK_EXPIRATION = OFF,
         CHECK_POLICY = OFF;


SQLserver will return the following message
Msg 15128, Level 16, State 1, Line 1
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.


Resolution

Aleter Login kalyanlogin With Password="samepassword"
go
Aleter Login kalyanlogin With CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;


 

Checking Agent Job Status Using TSQL

The below post describes how to verify a job status using TSQL. There are two different ways to identify the job status other than SSMS

Below are the two stored procedures.
sp_help_job -- Using this stored procedure current_execution status column we can identify the job status.
In this stored procedure the is a parameter called @job_aspect which returns different set of data based on it is value

@job_aspect Parameter values are - JOB, STEPS, SCHEDULES, TARGETS, ALL

-- To view only Job steps of a given job, below is the syntax

USE msdb ;
GO
EXEC dbo.sp_help_job
@job_name = N'FullBackups - Every Night',
@job_aspect = 'STEPS' ;
GO


xp_sqlagent_enum_jobs (undocumented extended stored procedure).

xp_sqlagent_enum_jobs -- State column describes the state of the job
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions

Syntax to execute
EXEC xp_sqlagent_enum_jobs 1, ''