PowerBi Embed report (public) not working - This content isn't available.

 PowerBi Embed report (public) error:

    

     Error Message: This content isn't available.

Solution: Delete the existing embed code and create new code.Goto Settings > Manage embed codes and delete web code

That's it!

 

Azure Synapse Storage Event Trigger : Activate/Publish error

Failed to activate

Failed to activate Storage Trigger (Trigger).

Issue: Event Grid Resource provider not registered.

After registering Event Grid resource, trigger activated successfully.

Data Factory Pipeline - Schema import failed

 "Schema import failed: The interactive authoring capability is not enabled on the integration runtime 'AutoResolveIntegrationRuntime'. Please enable interactive authoring first and retry the operation."

Go to Data Factory -> Manage -> Integration Runtimes -> AutoResolveIntegrationRuntime

Enable the interactive authoring.

Interactive authoring capability is used during authoring for functionalities like Test connection /Browse and Preview data / Import parameter / Import schema inside managed Virtual Network.

When enabled, the interactive authoring capability for this VNet integration runtime will disabled after the specified time interval of inactivity (i.e., no interactive commands).

Power BI App workspace creation is disabled

App workspace creation is disabled

Creation of app workspaces is disabled by your tenant admin, or you need permissions to create them. Please contact your tenant admin.

To enable the workspace creation, go to PowerBI Admin portal -> Tenant Settings -> Workspace Settings and enable settings. This option can be restricted to specific AD groups.

Copy Data from On-Prem to Azure Blob via SSIS failed with TLS error

Copy Data from On-Prem to Azure Blob via SSIS using Feature pack for Azure failed with TLS error:

Error:

Microsoft.DataTransfer.ClientLibrary.BinaryTransfer.Run(),''Type=Microsoft.WindowsAzure.Storage.StorageException,Message=The remote server returned an error: (400) Bad Request.,Source=Microsoft.WindowsAzure.Storage,StorageExtendedMessage=The TLS version of the connection is not permitted on this storage account.

Solution as per Microsoft:

Use TLS 1.2

The TLS version used by Azure Feature Pack follows system .NET Framework settings. To use TLS 1.2, add a REG_DWORD value named SchUseStrongCrypto with data 1 under the following two registry keys.

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319

Ref: https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-ver15#use-tls-12

SQL 2017 Installation stuck at Install_VSTA2015

SQL 2017 Installation stuck at "Install_VSTA2015_LangSupport_Cpu32_Action"

 

Log file (C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log):

 

Fix:

Run the below command from PowerShell, so the msiserver service will restart automatically

taskkill /F /FI "SERVICES eq msiserver"

PowerShell script to extract Windows Application Pool details

 $sb = {  Import-Module WebAdministration

         $webapps = Get-WebApplication

$list = @()

foreach ($webapp in get-childitem IIS:\AppPools\)

{

$name = "IIS:\AppPools\" + $webapp.name

$item = @{}

$item.server = $env:COMPUTERNAME

$item.WebAppName = $webapp.name

$item.Version = (Get-ItemProperty $name managedRuntimeVersion).Value

$item.State = (Get-WebAppPoolState -Name $webapp.name).Value

$item.UserIdentityType = $webapp.processModel.identityType

$item.Username = $webapp.processModel.userName

$obj = New-Object PSObject -Property $item

$list += $obj

}

$list | Select-object -Property  "server","WebAppName", "Version", "State", "UserIdentityType", "Username" 

}

$computers = Get-Content "C:\serverlist.txt"

foreach ($computer in $computers)  {

$computer

Invoke-command -ComputerName $computer -ScriptBlock $sb | export-csv C:\webAppPool_details.csv -append -NoTypeInformation

}

Adding disk to existing Storage Pool in SQL Failover cluster with Storage Spaces direct

      

For Storage Space direct Storage Pool, new disks should be unformatted disks on each node.

VM1

VM2

Newly added disks shown as available disks:

Run this command to add the newly added disks into cluster storage pool:

Now the disks merged to the Windows clustered storage:

Add the new disks into existing storage pool:

Extend the storage pool virtual disks:

After expanding the virtual disks, the space will be available to extend:

Configuring Windows Server 2016 Storage Spaces Direct for SQL Server Failover Cluster

         Storage Spaces Direct is a new feature available in Windows 2016 Datacenter edition that enables to make a storage solution in High Availability with local storage of each node.

This article describes step-by-step procedure on how to configure Storage Spaces direct for Windows failover cluster.

Requirements to S2D cluster nodes:

• Windows Server 2016 Datacenter edition;
• At least two servers in a cluster;
• In addition to the system drive, there must be at least three physical disks. All disks that you are going to add to the Storage Spaces Direct must be unformatted.

Preparing disks for Storage Spaces Direct:

Before we configure Storage Spaces Direct, we need to ensure the disks we are using are free of all partitions, otherwise they will not be included in the Storage Spaces Direct pool.

Servers: VM1 & VM2

Add empty volumes to each node:

VM1 

VM2

Now the new volumes will appear in the available disks in Storage Pool:

Note: PowerShell to find the Primordial pool (This is simply a holding pool for all unallocated disks that are connected to the server that you are currently managing. If you create a storage pool, you will be able to grab the disks from this pool to use in the new storage pool.)

Configure Storage Space Direct Cluster:

We have a functioning Windows cluster setup already, so now it’s time to setup Storage Spaces Direct.

Please note, this cmdlet will fail if you have less than 3 disks on the cluster nodes. In this scenario, there are two disks per node, a total of 4.

Creating Storage Pool in cluster via wizard:

Select the number of disks needed for pool creation. If you want to designate a physical disk as a hot spare, then select the “Hot Spare” allocation type instead of Automatic.

From above screenshots, we can see that we would not be able to proceed with config if selecting 2 disks, atleast 3 disks required to create a pool.

Note: Storage Space creation using PowerShell cmdlet

Below are a couple of PowerShell cmdlet examples that demonstrate the same provisioning action as described above using Server Manager. Create a storage pool using all the available physical disks. More information on pool creation using PowerShell cmdlet is here.

$PhysicalDisks = Get-StorageSubSystem -FriendlyName “Storage Spaces” | Get-PhysicalDisk -CanPool $True; 

New-StoragePool -FriendlyName CompanyData -StorageSubsystemFriendlyName “Storage Spaces*” -PhysicalDisks $PhysicalDisks;

From Failover Cluster Admin, we will now be able to see all the added physical disks from Storage Pool:

Create virtual disk (storage space):

Here, for testing environment, two volumes, one for data and one for logs have been created with 64KB allocation unit size, fixed provisioning and NTFS. SQL 2016 supports also using REFS however still don’t see any definitive documentation that recommends REFS over NTFS for SQL Server workloads.

New-Volume -StoragePoolFriendlyName "ClusterPool" -FriendlyName SQL17Data -FileSystem CSVFS_NTFS -AllocationUnitSize 65536 -Size 100GB -ProvisioningType Fixed -ResiliencySettingName Mirror

A small note on S2D storage options. It offers both two way and three-way mirror, by default two-way will be selected unless specified otherwise. This can be done through the PhysicalDiskRedundancy or the NumberofDataCopies parameters. A two-way mirror leaves more space available but can only sustain the loss of one disk, whereas 3-way can sustain the loss of two drives at the cost of more storage used.

You can also verify with PowerShell:

Get-ClusterSharedVolume

The CSV disks will show in Windows as two mountpoints under C:\ClusterStorage called Volume1 and Volume2. We can rename them to something more friendly:

We can verify the cluster shared volumes were created in the FCM GUI:

At this point, we’re good to go on the cluster and storage side. 

Powershell Script to update ODBC registry remotely

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
$regvalue = [Microsoft.VisualBasic.Interaction]::InputBox("Enter the ServerName", "Computer", "")

$Computer = 'TEST'
#$regvalue = 'Server1'

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computer)
$regKey= $reg.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC_NAME1",$true)

$ODBC_NAME1= $regKey.getValue("Server","$regvalue")

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $computer)
$regKey= $reg.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC_NAME2",$true)

$ODBC_NAME2= $regKey.getValue("Server","$regvalue")

[System.Windows.Forms.MessageBox]::Show("ODBC_NAME1 : $ODBC_NAME1       ODBC_NAME1 : $ODBC_NAME2","TEST ODBC updated",[System.Windows.Forms.MessageBoxButtons]::OKCancel,[System.Windows.Forms.MessageBoxIcon]::Information)

SQL 2014 - Backup Encryption

SQL Server backup encryption is introduced in SQL Server 2014. But why need of backup encryption when you have TDE feature since SQL2008?

TDE is an encyption technique supported only on Enterpise Edition but where as SQL 2014 backup encryption is supported in Standard, Business Intelligence and Enterprise editions. Also with TDE, it implies some CPU overhead and the backup file compression ratio is then very low. So if an organizations using TDE just for sake of encrypting backup files or using any other 3rd party tool, then this backup encryption can be an alternative solution.
The supported SQL Server 2014 backup encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES.


/*****Create Database Master Key on Master DB*****/
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
--Create an encryption certificate
Use Master
GO
CREATE CERTIFICATE SQLbackupEncrptCert
WITH SUBJECT = 'SQL Backup Encryption Certificate';

/*****Backup Service Master Key:*****/
USE master
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_SMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO

/*****Backup Database Master Key*****/
BACKUP MASTER KEY
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLTest_DMK.key'
ENCRYPTION BY PASSWORD = 'xxxxxxxxx';
GO

/*****Backup Certificate*****/
Use Master
GO
BACKUP CERTIFICATE SQLbackupEncrptCert
TO FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert.cert'
WITH PRIVATE KEY
(
FILE = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\SQLbackupEncrptCert_PK.key',
ENCRYPTION BY PASSWORD = 'xxxxxxxxx'
);
GO

/*****Backup database with encryption*****/
BACKUP DATABASE TestDb
TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQLTWO\MSSQL\Backup\TestDB_SQL2014_Encrypt.bak'
WITH COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = SQLbackupEncrptCert
)

Limitations:
Appending to an existing backup set option is not supported for encrypted backups.
SQL Server Express and SQL Server Web do not support backup encryption but an encrypted backup can be restored.

SQL 2014/2016 Features

Features	SQL2014	SQL2016	Feature Description
Always Encrypted **		#	SQL Server 2016 new features are upgraded with Always Encrypted feature, which enables to encrypt database. This lets client application owners to manage which application should get the access the confidential data. This is done by letting the client application have the encryption key which is never passed to server.
Dynamic Data Masking **		#	This element has been added to customize the security of data according to the viewers. Dynamic Data Masking allows users to mask the confidential columns in a table for users who are not authorized for viewing.
Row Level Security **		#	Row level security, it provides a mechanism to control row level read and write access based on the user's context data like identity, role/group membership, session/connection specific information. Restricting rows will be done by filter predicates defined in inline table value function.
Stretch Database **		#	The Stretch Database feature provides a method to stretch the storage of On-Premise database to Azure SQL Database. This feature will make it easy to archive information to a cheaper storage media without having to change any actual application code and thereby maximizing performance.
JSON Support **		#	JSON stands for Java Script Object Notation. Parsing & storing of JSON as relational data & exporting relational data to JSON, as it is becoming a popular format to store NoSQL/Unstructured data.
PolyBase **		#	PolyBase is another significant addition which allows querying distributed data sets. This will allow you to use T-SQL statements to query Hadoop or SQL Azure blob storage.
R Comes to SQL Server **		#	With Microsoft's purchase of Revolution Analytics, now able to incorporate R to support advance analytics against big data right inside of SQL Server. By incorporating R processing into SQL Server, data scientists will be able to take the existing R code and run it right inside the SQL Server database engine.
Temporal Table **		#	Temporal table is defined as a table holding old versions of rows within a base table. If the database is having temporal tables, SQL Server can automatically manage shifting old row versions to the temporal table whenever it is updated. Users who are planning to manage row versioning might find this modification quite helpful.
Managing Locks in Online Index Rebuilds *	#	#	With SQL 2014 and later version, have an option to specify how long an index rebuild should wait for the locks required. This option also allows us to selectively terminate blocking processes caused by online index rebuilds.
Buffer Pool Extension *	#	#	In SQL Server 2014, use a fast SSD drive as a buffer pool extension (BPE). This extension is used only when main server memory starts to run out, and provides faster-than-spinning-disk-I/O access to data that's already been read from disk. This provides improved random I/O throughput, reduced latency, and a new caching architecture that enables us to take advantage of low-cost SSD drives to expand available cache, instead of just adding RAM.
In Memory OLTP *	#	#	This engine provides a lock and latch free environment for OLTP workloads. It enables to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures. SQL 2016 - Has a major face-lift, increasing the maximum size of a table to an incredible 2 TB, up from 256 GB.
Partition Enhancements *	#	#	Individual partitions may now be rebuilt individually. If the most recent partitions become fragmented due to activity it's no longer necessary to rebuild indexes on the entire table.
Resource Governor Improvements *	#	#	Resource Governor now supports management of IO as well as CPU and Memory that were previously supported.
Columnstore Indexes *	#	#	Coulmnstore index funcaitonality supports highly aggregated data warehouse queries.SQL Server 2014 enhances to add support for creating Clustered Columnstore Index in addition to the non-clustered columnstore index which is for read-only queries. While in In SQL 2016, Columnstore indexes are no longer read-only.
Always On Improvements *	#	#	AlwaysOn Secondaries: SQL 2014 doubled the number of AlwaysOn readable secondary replica databases to eight, and SQL Server 2016 adds a third synchronous replica. More Reliable Secondaries: Previously (SQL 2012) several factors could result in secondaries being dropped offline which has been addressed in 2014/2016 and 2016 Secondary AlwaysOn replicas now automatically load balance READ workloads using round-robin. Scale to the Cloud: SQL 2014/2016 allows for Hybrid systems as you can now use Azure VMs as AlwaysOn AG replicas. AlwaysOn Availability Groups feature is now available in SQL 2016 Standard Edition with the limitation to one database and one non readable secondary replica as "Basic Availability Group (BAG) but allows multiple BAGs.
Improved Azure Integration *	#	#	SQL Server 2014 as a hybrid cloud platform, the implication being that SQL Server databases can now be more easily integrated with Windows Azure. SQL Server 2014 also lets store the data and log files associated with an on-premises database in Azure's storage. In addition, SQL Server Management Studio provides a deployment wizard that lets easy migration of existing on-premises databases to an Azure virtual machine.
BackupEnhancements (Both Standard and Enterprise)	#	#	Backup to Azure:With this new feature, backups can be stored offsite (in the cloud-Azure block blob storage) providing safe storage of backup data, as opposed to SQL Server 2014's limitation to backup online to page blob storage and no system databases and limited to 1 TB. In SQL 2016, the total backup size limit is increased to more than 12 TB and includes system databases. Smart Backup to Azure. With Smart Backups SQL Server determines whether a full or incremental backup is needed and backs up accordingly to Azure. Encryption: SQL Server will have the ability to encrypt the data while the backup is in progress. Can encrypt the database backup by specifying Encryption Algorithm and an Encryptor (Certificate or Asymmetric Key) while creating database backups.

* Enterprise Edition Only
**SQL 2016 Enterprise - Still in CTP

.NET 3.5 Installation failed on Windows Server 2012 R2

.NET 3.5 Installation failed on Windows Server 2012 R2 because the server configured to use WSUS instead of Windows update service.

Try the below steps to install .NET3.5
1. Goto group policy (run->gpedit.msc)
2. Navigate Computer Configuration ->Administrative Templates ->System
3. Enable Specify settings for optional component installation and computer repair.
4. Select "Conatct Windows Update directly to download repair content instead of WSUS.
5. Now try the .Net 3.5 install from Add Roles and Features Wizard.

Ref : https://support.microsoft.com/en-us/kb/2734782

Microsoft Dynamics GP - An error occurred while using the BCP utility -- data was not correctly copied to the server

Below error will occur when you run GP utilities for first time.

An error occurred while using the BCP utility -- data was not correctly copied to the server

Run GP utilities as Administrator, you will be able to open it.

Ref : https://support.microsoft.com/en-us/kb/869315

Log Shipping Copy failed due to compromise security

All of sudden Log shipping copy job failing with below error
"The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you."

To resolve this issue configure the firewall - TCP port 88 and UDP port 88 are not blocked for either domain.

There is a article in MS - http://support.microsoft.com/kb/938457 explaining this error will occur when you are dealing with multi domain. 

Make sure SQL Agent services restarted after the change.