Enabling Temporal Table on In-Memory OLTP Table

SELECT * FROM Sales.SpecialOfferProduct_inmem ALTER TABLE Sales.SpecialOfferProduct_inmem ADD StartTime DATETIME2 GO ALTER TABLE Sales.SpecialOfferProduct_inmem ADD EndTime DATETIME2 GO UPDATE Sales.SpecialOfferProduct_inmem SET StartTime = ‘19000101 00:00:00.0000000’, EndTime = ‘99991231 23:59:59.9999999’ GO ALTER TABLE Sales.SpecialOfferProduct_inmem ALTER COLUMN StartTime DATETIME2 NOT NULL GO ALTER TABLE Sales.SpecialOfferProduct_inmem ALTER COLUMN EndTime DATETIME2 NOT NULL GO ALTER TABLE Sales.SpecialOfferProduct_inmem ADD PERIOD […]

Introduction to SQL Server 2016 Temporal Tables

FROM : BOL Temporal table is released with SQL Server 2016.It was introduced first with ANSI SQL 2011 and is now supported in SQL Server 2016. *Do not mistake Temporal Tables with Temp Table.* Temporal table provides information about data stored in the table at any point in time rather than only the data that […]

deleting *.cache files from SQL Server

Cache files are generated when Data collection is enabled and Collection set is running. You can delete the files if not required after disabling the Data Collector. USE msdb; GO EXEC dbo.sp_syscollector_disable_collector; To enabled Data Collector USE msdb; GO EXEC dbo.sp_syscollector_enable_collector ;    

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider SQLNCLI.1 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. End Error

The requested OLE DB provider is not registered — perhaps no 64-bit provider is available. And what OLEDB provider you are using? Does it exist for 64-bit, is it installed? The error means that the OLDEDB provider you are using to connect to SQL server is only support on 32 bit run-times To resolve the issue […]

Can’t Expand MSDB in Integration Services Server

A recently built server with names instances fails to expand MSDB folder in Integration services. The Error which pops is very generic which can be seen in below image To get this working you need to update the MsDtsSrvr.ini file with correct sql instance name <ServerName> ServerName\SqlInstanceName</ServerName>   MsDtsSrvr.ini file can be found at installation […]

TDE : Transparent Data Encryption

USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd’; go CREATE CERTIFICATE DB_TDE_Cert WITH SUBJECT = ‘DBTDE_Cert’; –Backup the key USE master; OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘P@ssw0rd’; BACKUP MASTER KEY TO FILE = ‘C:\data\EncryptionDemo\DBTDE_Cert.key’ ENCRYPTION BY PASSWORD =’P@ssw0rd’; GO BACKUP CERTIFICATE DB_TDE_Cert TO FILE = ‘C:\data\EncryptionDemo\DBTDE_Cert.bak’ WITH PRIVATE KEY ( […]

Always Encrypted

*This is NOT TDE* New feature in SQL Server 2016 : a new capability that protects data at rest and in motion. Always Encrypted  encrypts data of individual columns end -to -end(protects data at rest and in motion). This feature currently support Ado.net & Sql Client of ADO.Net 4.6. Lets see how to configure this feature on New […]

Encrypting connections to SQL Server – SSL

The post is to demonstrate how to encrypt connection to SQL Server using SSL. At the bottom of the page you can see the video which demonstrates how to create a certificate and configure SQL Server to use SSL. SSL : Secure Sockets Layer is the most commonly used for web based client or native […]

Script LOGIN HASHED PASSWORD

LOGINPROPERTY function gives you the stored has password. SELECT LOGINPROPERTY(‘LoginName’,’PASSWORDHASH’); Output 0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB To use this hashed password , you have to use HASHED keyword. CREATE LOGIN LoginName WITH PASSWORD=0x020056986E23A5C403130F4B2DAD7ADBC835B9A662E70AE1937A7B7097DE2B36C58C3FDCB8912CF28DERP0A348918F559D53BF8868BA42D7FE1E400AE636862F939BC1DACFAEE3DB HASHED;  

Database Mirroring login attempt by user ‘Domain\Login’ failed with error: ‘Connection handshake failed. The login ‘Domain\Login’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: xx.xx.xx.xxx]

While configuring database mirroring we encountered below error message with GUI and in SQL Server Error Log   GUI Error:  Error : Database Mirroring login attempt by user ‘Domain\Login’ failed with error: ‘Connection handshake failed. The login ‘Domain\Login’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: xx.xx.xx.xxx] Reason : Normally this error popup […]

PowerShell SQL Connection Test

In an Environment where you have 100’s of SQL instances and you need to test the connectivity to each of the instance or you need to deploy a script on each of the instance after verifying the connectivity to the instance.You need to have some sort of script which will connect to each instance and […]