🏭🗒️Microsoft Fabric: Shortcuts [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 8-Dec-2024

[Microsoft Fabric] Shortcut

• {def} object that points to other internal or external storage location (aka shortcut) [1] and that can be used for data access
• {goal} unifies existing data without copying or moving it [2]
• ⇒ data can be used multiple times without being duplicated [2]
• {benefit} helps to eliminate edge copies of data [1]
• {benefit} reduces process latency associated with data copies and staging [1]
• is a mechanism that allows to unify data across domains, clouds, and accounts through a namespace [1]
• ⇒ allows creating a single virtual data lake for the entire enterprise [1]
• ⇐ available in all Fabric experiences [1]
• ⇐ behave like symbolic links [1]
• independent object from the target [1]
• appear as folder [1]
• can be used by workloads or services that have access to OneLake [1]
• transparent to any service accessing data through the OneLake API [1]
• can point to 
• OneLake locations
• ADLS Gen2 storage accounts
• Amazon S3 storage accounts
• Dataverse
• on-premises or network-restricted locations via PDF 
• {capability} create shortcut to consolidate data across artifacts or workspaces, without changing data's ownership [2]
• {capability} data can be compose throughout OneLake without any data movement [2]
• {capability} allow instant linking of data already existing in Azure and in other clouds, without any data duplication and movement [2]
• ⇐ makes OneLake the first multi-cloud data lake [2]
• {capability} provides support for industry standard APIs
• ⇒ OneLake data can be directly accessed via shortcuts by any application or service [2]
• {operation} creating a shortcut
• can be created in 
• lakehouses
• KQL databases
• ⇐ shortcuts are recognized as external tables [1]
• can be created via 
• Fabric UI 
• REST API
• can be created across items [1]
• the item types don't need to match [1]
• e.g. create a shortcut in a lakehouse that points to data in a data warehouse [1]
• [lakehouse] tables folder
• represents the managed portion of the lakehouse 
• shortcuts can be created only at the top level [1]
• ⇒ shortcuts aren't supported in other subdirectories [1]
• if shortcut's target contains data in the Delta\Parquet format, the lakehouse automatically synchronizes the metadata and recognizes the folder as a table [1]
• [lakehouse] files folder
• represents the unmanaged portion of the lakehouse [1]
• there are no restrictions on where shortcuts can be created [1]
• ⇒ can be created at any level of the folder hierarchy [1]
• ⇐table discovery doesn't happen in the Files folder [1]
• {operation} renaming a shortcut
• {operation} moving a shortcut
• {operation} deleting a shortcut 
• doesn't affect the target [1]
• ⇐ only the shortcut object is deleted [1]
  
• ⇐ the shortcut target remains unchanged [1]
• shortcuts don't perform cascading deletes [1]
• moving, renaming, or deleting a target path can break the shortcut [1]
• {operation} delete file/folder
• file or folder within a shortcut can be deleted when the permissions in the shortcut target allows it [1]
• {permissions} users must have permissions in the target location to read the data [1]
• when a user accesses data through a shortcut to another OneLake location, the identity of the calling user is used to authorize access to the data in the target path of the shortcut [1]
• when accessing shortcuts through Power BI semantic models or T-SQL, the calling user’s identity is not passed through to the shortcut target [1]
•  the calling item owner’s identity is passed instead, delegating access to the calling user [1]
• OneLake manages all permissions and credentials
• {feature} shortcut caching 
• {def} mechanism used to reduce egress costs associated with cross-cloud data access [1]
• when files are read through an external shortcut, the files are stored in a cache for the Fabric workspace [1]
• subsequent read requests are served from cache rather than the remote storage provider [1]
• cached files have a retention period of 24 hours
• each time the file is accessed the retention period is reset [1]
• if the file in remote storage provider is more recent than the file in the cache, the request is served from remote storage provider and the updated file will be stored in cache [1]
• if a file hasn’t been accessed for more than 24hrs it is purged from the cache [1]
• {restriction} individual files greater than 1GB in size are not cached [1]
• {restriction} only GCS, S3 and S3 compatible shortcuts are supported [1]
• {limitation} maximum number of shortcuts [1] 
• per Fabric item: 100,000
• in a single OneLake path: 10
• direct shortcuts to shortcut links: 5
• {limitation} ADLS and S3 shortcut target paths can't contain any reserved characters from RFC 3986 section 2.2 [1]
• {limitation} shortcut names, parent paths, and target paths can't contain "%" or "+" characters [1]
• {limitation} shortcuts don't support non-Latin characters[1]
• {limitation} Copy Blob API not supported for ADLS or S3 shortcuts[1]
• {limitation} copy function doesn't work on shortcuts that directly point to ADLS containers
• {recommended} create ADLS shortcuts to a directory that is at least one level below a container [1]
• {limitation} additional shortcuts can't be created inside ADLS or S3 shortcuts [1]
• {limitation} lineage for shortcuts to Data Warehouses and Semantic Models is not currently available[1]
• {limitation} it may take up to a minute for the Table API to recognize new shortcuts [1]

Previous Post <<||>>  Next Post

References:
[1] Microsoft Fabric (2024) OneLake shortcuts [link]
[2] Microsoft Fabric (2024) Fabric Analyst in a Day [course notes]

Acronyms:

ADLS - Azure Data Lake Storage

AWS - Amazon Web Services

GCS - Google Cloud Storage

KQL - Kusto Query Language

OPDG - on-premises data gateway

💠🛠️SQL Server: Administration (Troubleshooting Login Failed for User)

    Since the installation of an SQL Server 2017 on a virtual machine (VM) in the Microsoft Cloud started to appear in the error log records with the following message:

Login failed for user '<domain>\<computer>$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 5.

   From the text it seemed like a permission problem, thing confirmed by the documentation (see [1]), the Error Number and State correspond to a „User Id is not valid“ situation. In a first step I attempted to give permissions to the local account (dollar sign included). The account wasn’t found in the Active Directory (AD), though by typing the account directly in the “Login name” I managed to give temporarily sysadmin permission to the account. The error continued to appear in the error log. I looked then at the accounts under which the SQL Services run - nothing suspect in there.

   Except the error message, which was appearing with an alarming frequency (a few seconds apart), everything seemed to be working on the server. The volume of  records (a few hundred thousands over a few days) bloating the error log, as well the fact that I didn’t knew what’s going on made me take the time and further investigate the issue.

  Looking today at the Windows Logs for Applications I observed that the error is caused by an account used for the Microsoft SQL Server IaaS Agent and IaaS Query Service. Once I gave permissions to the account the error disappeared.

   The search for a best practice on what permissions to give to the IaaS Agent and IaaS Query Service lead me to [2]. To quote, the “Agent Service needs Local System rights to be able to install and configure SQL Server, attach disks and enable storage pool and manage automated security patching of Windows and SQL server”, while the “IaaS Query Service is started with an NT Service account which is a Sys Admin on the SQL Server”. In fact, this was the only resource I found that made a reference to the IaaS Query Service.

   This was just one of the many scenarios in which the above error appears. For more information see for example  [3], [4] or [5].

References:
[1] Microsoft (2017) MSSQLSERVER_18456 [Online] Available from: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-2017
[2] SQL Database Engine Blog (2018) SQL Server IaaS Extension Query Service for SQL Server on Azure VM, by Mine Tokus Altug [Online] Available from:  https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/10/25/sql-server-iaas-extension-query-service-for-sql-server-on-azure-vm/
[3] Microsoft Support (2018) "Login failed for user" error message when you log on to SQL Server [Online] Available from: https://support.microsoft.com/en-sg/help/555332/login-failed-for-user-error-message-when-you-log-on-to-sql-server
[4] Microsoft Technet (2018) How to Troubleshoot Connecting to the SQL Server Database [Online] Available from: Engine https://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-the-sql-server-database-engine.aspx 
[5] Microsoft Blogs (2011)Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server, by Sakthivel Chidambaram [Online] Available from: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/06/troubleshoot-connectivitylogin-failures-18456-state-x-with-sql-server/

💠🛠️SQL Server: Administration (Database Recovery on SQL Server 2017)

I installed today SQL Server 2017 CTP 2.1 on my Lab PC without any apparent problems. It was time to recreate some of the databases I used for testing. As previously I had an evaluation version of SQL Server 2016, it expired without having a backup for one of the databases. I could recreate the database from scripts and reload the data from various text files. This would have been a relatively laborious task (estimated time > 1 hour), though the chances were pretty high that everything would go smoothly. As the database is relatively small (about 2 GB) and possible data loss was neglectable, I thought it would be possible to recover the data from the database with minimal loss in less than half of hour. I knew this was possible, as I was forced a few times in the past to recover data from damaged databases in SQL Server 2005, 2008 and 2012 environments, though being in a new environment I wasn’t sure how smooth will go and how long it would take.  

Plan A - Create the database with  ATTACH_REBUILD_LOG option:

As it seems the option is available in SQL Server 2017, so I attempted to create the database via the following script:
 

CREATE DATABASE  ON 
(FILENAME='I:\Data\.mdf') 
FOR ATTACH_REBUILD_LOG 

And as expected I run into the first error:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "I:\Data\.mdf". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It looked like a permissions problem, though I wasn’t entirely sure which account is causing the problem. In the past I had problems with the Administrator account, so it was the first thing to try. Once I removed the permissions for Administrator account to the folder containing the database and gave it full control permissions again, I tried to create the database anew using the above script, running into the next error:

File activation failure. The physical file name "D:\Logs\_log.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1 Could not open new database ''. CREATE DATABASE is aborted.

This approach seemed to lead nowhere, so it was time for Plan B.

Plan B - Recover the database into an empty database with the same name:

Step 1: Create a new database with the same name, stop the SQL Server, then copy the old file over the new file, and delete the new log file manually. Then restarted the server. After the restart the database will appear in Management Studio with the SUSPECT state.

Step 2: Set the database in EMERGENCY mode:

ALTER DATABASE  SET EMERGENCY, SINGLE_USER

Step 3: Rebuild the log file:

ALTER DATABASE <database_name> 

REBUILD LOG ON (Name=’_Log', 

FileName='D:\Logs\.ldf')

The rebuild worked without problems.

Step 4: Set the database in MULTI_USER mode:

ALTER DATABASE  SET MULTI_USER 

Step 5: Perform a consistency check:

DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSG 

After 15 minutes of work the database was back online.

Warnings:
Always attempt to recover the data for production databases from the backup files! Use the above steps only if there is no other alternative!
The consistency check might return errors. In this case one might need to run CHECKDB with REPAIR_ALLOW_DATA_LOSS several times [2], until the database was repaired.
After recovery there can be problems with the user access. It might be needed to delete the users from the recovered database and reassign their permissions!  

Resources:
[1] In Recovery (2008) Creating, detaching, re-attaching, and fixing a SUSPECT database, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/ 
[2] In Recovery (2009) Misconceptions around database repair, by Paul S Randal [Online] Available from: https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
[3] Microsoft Blogs (2013) Recovering from Log File Corruption, by Glen Small [Online] Available from: https://blogs.msdn.microsoft.com/glsmall/2013/11/14/recovering-from-log-file-corruption/

🛢DBMS: Object Permissions (Definitions)

"These are permissions that enable a user to work with data in an object. For example, SELECT is the object permission that enables a user to read data from a table object." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"Permission based on a table or view; controls the ability to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table or view." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"An attribute that controls the ability to perform operations on an object. For example, table or view permissions control which users can execute SELECT, INSERT, UPDATE, and DELETE statements against the table or view." (Anthony Sequeira & Brian Alderman, "The SQL Server 2000 Book", 2003)

"A permission on a database object that controls how the object can be accessed." (Marilyn Miller-White et al, "MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance 70-444", 2007)

"Permissions that regulate the use of certain commands (data modification commands, plus select, truncate table and execute) to specific tables, views or columns." (Karen Paulsell et al, "Sybase SQL Server: Performance and Tuning Guide", 1996)

"Object permissions regulate a user’s ability to work with the data contained in the database." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

🛢DBMS: Permissions (Definitions)

"A permission is a right to do something in a database. Examples include performing a database function (such as creating table) or working with an object (INSERT)." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

[statement permissions:] "These are database permissions and enable users to create objects, drop objects, or modify objects in a database. Statement permissions do not work with data, but rather work with the containers that hold the data." (Owen Williams, "MCSE TestPrep: SQL Server 6.5 Design and Implementation", 1998)

"Authorization that enforces database security. SQL Server permissions specify the Transact-SQL statements, views, and stored procedures each user is authorized to use. The ability to assign permissions is determined by each user's status. There are two types of permissions: object permissions and statement permissions." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

[statement permission:] "Permission that controls the execution of Transact-SQL statements that create database objects or perform certain administrative tasks. Can be granted, revoked, or denied." (Microsoft Corporation, "SQL Server 7.0 System Administration Training Kit", 1999)

"These are permissions that regulate a user’s ability to create structures that hold data, such as tables and views." (Joseph L Jorden & Dandy Weyn, "MCTS Microsoft SQL Server 2005: Implementation and Maintenance Study Guide - Exam 70-431", 2006)

"A privilege that you grant to a principle. When authorized, the principle may then interact with one or more securables." (Robert D. Schneider and Darril Gibson, "Microsoft SQL Server 2008 All-In-One Desk Reference For Dummies", 2008)

"Operations that can be applied to or done with an object. Example file permissions are read, write, and delete." (Mark Rhodes-Ousley, "Information Security: The Complete Reference", 2nd Ed., 2013)

"Permissions placed on objects within a database. Database permissions specify which actions a database user can perform on tables, views, stored procedures, and other objects." (Mark Rhodes-Ousley, "Information Security: The Complete Reference" 2nd Ed., 2013)

"The definitions of what object access actions are permitted for a specific user or group." (Weiss, "Auditing IT Infrastructures for Compliance" 2nd Ed, 2015)

"The type of authorized interactions that a subject can have with an object. Examples include read, write, execute, add, modify, and delete." (Shon Harris & Fernando Maymi, "CISSP All-in-One Exam Guide" 8th Ed, 2018)