On Homomorphic Encryption

Probably, one of the most exciting recent developments in the field of cryptography is the emergence of homomorphic encryption which is a type of encryption that allows computations to be performed on encrypted data while it remains encrypted, in other words, without the need to decrypt it first. This means that sensitive data can be kept confidential while still being used by third-parties.

This is achieved through the use of special encryption algorithms that preserve the mathematical structures of the plaintext data, allowing meaningful computations to be performed on the encrypted data while preventing unauthorized access to the actual plaintext data (which is never exposed and remain secure).

This technology is particularly useful in situations where privacy and security are of great importance, such as in the healthcare industry, where patient data must be kept confidential, or in financial services, where sensitive data such as bank account information needs to be processed securely. 

Probabilistic Estimation of the Algebraic Degree of Boolean Functions

Recently, our cryptography paper on "Probabilistic estimation of the algebraic degree of Boolean functions" was published in Springer Journal as a result of about 3 years of research: https://lnkd.in/eyEw5pce

𝐀𝐛𝐬𝐭𝐫𝐚𝐜𝐭: 𝘛𝘩𝘦 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘥𝘦𝘨𝘳𝘦𝘦 𝘪𝘴 𝘢𝘯 𝘪𝘮𝘱𝘰𝘳𝘵𝘢𝘯𝘵 𝘱𝘢𝘳𝘢𝘮𝘦𝘵𝘦𝘳 𝘰𝘧 𝘉𝘰𝘰𝘭𝘦𝘢𝘯 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯𝘴 𝘶𝘴𝘦𝘥 𝘪𝘯 𝘤𝘳𝘺𝘱𝘵𝘰𝘨𝘳𝘢𝘱𝘩𝘺. 𝘞𝘩𝘦𝘯 𝘢 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯 𝘪𝘯 𝘢 𝘭𝘢𝘳𝘨𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘷𝘢𝘳𝘪𝘢𝘣𝘭𝘦𝘴 𝘪𝘴 𝘯𝘰𝘵 𝘨𝘪𝘷𝘦𝘯 𝘦𝘹𝘱𝘭𝘪𝘤𝘪𝘵𝘭𝘺 𝘪𝘯 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘯𝘰𝘳𝘮𝘢𝘭 𝘧𝘰𝘳𝘮, 𝘪𝘵 𝘪𝘴 𝘶𝘴𝘶𝘢𝘭𝘭𝘺 𝘯𝘰𝘵 𝘧𝘦𝘢𝘴𝘪𝘣𝘭𝘦 𝘵𝘰 𝘤𝘰𝘮𝘱𝘶𝘵𝘦 𝘪𝘵𝘴 𝘥𝘦𝘨𝘳𝘦𝘦, 𝘴𝘰 𝘸𝘦 𝘯𝘦𝘦𝘥 𝘵𝘰 𝘦𝘴𝘵𝘪𝘮𝘢𝘵𝘦 𝘪𝘵. 𝘞𝘦 𝘱𝘳𝘰𝘱𝘰𝘴𝘦 𝘢 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘴𝘵𝘪𝘤 𝘵𝘦𝘴𝘵 𝘧𝘰𝘳 𝘥𝘦𝘤𝘪𝘥𝘪𝘯𝘨 𝘸𝘩𝘦𝘵𝘩𝘦𝘳 𝘵𝘩𝘦 𝘢𝘭𝘨𝘦𝘣𝘳𝘢𝘪𝘤 𝘥𝘦𝘨𝘳𝘦𝘦 𝘰𝘧 𝘢 𝘉𝘰𝘰𝘭𝘦𝘢𝘯 𝘧𝘶𝘯𝘤𝘵𝘪𝘰𝘯 𝘧 𝘪𝘴 𝘣𝘦𝘭𝘰𝘸 𝘢 𝘤𝘦𝘳𝘵𝘢𝘪𝘯 𝘷𝘢𝘭𝘶𝘦 𝘬. 𝘐𝘧 𝘵𝘩𝘦 𝘥𝘦𝘨𝘳𝘦𝘦 𝘪𝘴 𝘪𝘯𝘥𝘦𝘦𝘥 𝘣𝘦𝘭𝘰𝘸 𝘬, 𝘵𝘩𝘦𝘯 𝘧 𝘸𝘪𝘭𝘭 𝘢𝘭𝘸𝘢𝘺𝘴 𝘱𝘢𝘴𝘴 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵, 𝘰𝘵𝘩𝘦𝘳𝘸𝘪𝘴𝘦 𝘧 𝘸𝘪𝘭𝘭 𝘧𝘢𝘪𝘭 𝘦𝘢𝘤𝘩 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦 𝘰𝘧 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘸𝘪𝘵𝘩 𝘢 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘥𝘵_𝘬(𝘧), 𝘸𝘩𝘪𝘤𝘩 𝘪𝘴 𝘤𝘭𝘰𝘴𝘦𝘭𝘺 𝘳𝘦𝘭𝘢𝘵𝘦𝘥 𝘵𝘰 𝘵𝘩𝘦 𝘢𝘷𝘦𝘳𝘢𝘨𝘦 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘮𝘰𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘰𝘧 𝘥𝘦𝘨𝘳𝘦𝘦 𝘬 𝘰𝘧 𝘵𝘩𝘦 𝘱𝘰𝘭𝘺𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘸𝘩𝘪𝘤𝘩 𝘢𝘳𝘦 𝘢𝘧𝘧𝘪𝘯𝘦 𝘦𝘲𝘶𝘪𝘷𝘢𝘭𝘦𝘯𝘵 𝘵𝘰 𝘧. 𝘛𝘩𝘦 𝘵𝘦𝘴𝘵 𝘩𝘢𝘴 𝘢 𝘨𝘰𝘰𝘥 𝘢𝘤𝘤𝘶𝘳𝘢𝘤𝘺 𝘰𝘯𝘭𝘺 𝘪𝘧 𝘵𝘩𝘪𝘴 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘥𝘵_𝘬(𝘧) 𝘰𝘧 𝘧𝘢𝘪𝘭𝘪𝘯𝘨 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘪𝘴 𝘯𝘰𝘵 𝘵𝘰𝘰 𝘴𝘮𝘢𝘭𝘭. 𝘞𝘦 𝘪𝘯𝘪𝘵𝘪𝘢𝘵𝘦 𝘵𝘩𝘦 𝘴𝘵𝘶𝘥𝘺 𝘰𝘧 𝘥𝘵_𝘬(𝘧) 𝘣𝘺 𝘴𝘩𝘰𝘸𝘪𝘯𝘨 𝘵𝘩𝘢𝘵 𝘪𝘯 𝘵𝘩𝘦 𝘱𝘢𝘳𝘵𝘪𝘤𝘶𝘭𝘢𝘳 𝘤𝘢𝘴𝘦 𝘸𝘩𝘦𝘯 𝘵𝘩𝘦 𝘥𝘦𝘨𝘳𝘦𝘦 𝘰𝘧 𝘧 𝘪𝘴 𝘢𝘤𝘵𝘶𝘢𝘭𝘭𝘺 𝘦𝘲𝘶𝘢𝘭 𝘵𝘰 𝘬, 𝘵𝘩𝘦 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺 𝘸𝘪𝘭𝘭 𝘣𝘦 𝘪𝘯 𝘵𝘩𝘦 𝘪𝘯𝘵𝘦𝘳𝘷𝘢𝘭 (0.288788, 0.5], 𝘢𝘯𝘥 𝘵𝘩𝘦𝘳𝘦𝘧𝘰𝘳𝘦 𝘢 𝘴𝘮𝘢𝘭𝘭 𝘯𝘶𝘮𝘣𝘦𝘳 𝘰𝘧 𝘳𝘶𝘯𝘴 𝘰𝘧 𝘵𝘩𝘦 𝘵𝘦𝘴𝘵 𝘸𝘪𝘭𝘭 𝘣𝘦 𝘴𝘶𝘧𝘧𝘪𝘤𝘪𝘦𝘯𝘵 𝘵𝘰 𝘨𝘪𝘷𝘦, 𝘸𝘪𝘵𝘩 𝘷𝘦𝘳𝘺 𝘩𝘪𝘨𝘩 𝘱𝘳𝘰𝘣𝘢𝘣𝘪𝘭𝘪𝘵𝘺, 𝘵𝘩𝘦 𝘤𝘰𝘳𝘳𝘦𝘤𝘵 𝘢𝘯𝘴𝘸𝘦𝘳. 𝘌𝘹𝘢𝘤𝘵 𝘷𝘢𝘭𝘶𝘦𝘴 𝘰𝘧 𝘥𝘵_𝘬(𝘧) 𝘧𝘰𝘳 𝘢𝘭𝘭 𝘵𝘩𝘦 𝘱𝘰𝘭𝘺𝘯𝘰𝘮𝘪𝘢𝘭𝘴 𝘪𝘯 8 𝘷𝘢𝘳𝘪𝘢𝘣𝘭𝘦𝘴 𝘸𝘦𝘳𝘦 𝘤𝘰𝘮𝘱𝘶𝘵𝘦𝘥 𝘶𝘴𝘪𝘯𝘨 𝘵𝘩𝘦 𝘳𝘦𝘱𝘳𝘦𝘴𝘦𝘯𝘵𝘢𝘵𝘪𝘷𝘦𝘴 𝘭𝘪𝘴𝘵𝘦𝘥 𝘣𝘺 𝘏𝘰𝘶 𝘢𝘯𝘥 𝘣𝘺 𝘓𝘢𝘯𝘨𝘦𝘷𝘪𝘯 𝘢𝘯𝘥 𝘓𝘦𝘢𝘯𝘥𝘦𝘳.

New Features in SQL Server 2022 for Developers

SQL Server 2022 brings a comprehensive suite of features designed to enhance the development experience, improve performance, and strengthen security. By leveraging these capabilities, developers can create more efficient, scalable, and secure applications, positioning their organisations to effectively handle modern data challenges and requirements. The focus on intelligent processing, machine learning, data integration, and robust security aligns with the evolving landscape of application development and data management.

Here’s a detailed overview of the new features in SQL Server 2022 tailored for developers.

1. Intelligent Query Processing Enhancements

Overview: SQL Server 2022 continues to build on the Intelligent Query Processing (IQP) capabilities introduced in previous versions. The aim is to enhance query performance automatically without requiring significant code changes. This means that developers can achieve better performance for their applications without extensive rewrites.

Key Feature:

• Parameter Sensitive Plan Optimisation: This feature allows SQL Server to create and maintain multiple execution plans for the same query based on different parameter values. It identifies the most efficient plan based on runtime parameter values rather than relying solely on the static plan generated during compilation.

Example Scenario: Consider a financial application that retrieves transaction records based on different filters, such as dates and account numbers. With parameter-sensitive plan optimization, SQL Server will adaptively select the best execution plan based on the most commonly accessed account numbers or date ranges, leading to reduced execution times and better resource utilisation.

2. Built-in Machine Learning Services

Overview: SQL Server 2022 provides built-in support for machine learning services directly within the database engine. This integration allows developers to run R and Python scripts without the need for external tools.

Key Features:

• Direct Data Access: R and Python scripts can access SQL Server data directly, allowing for real-time analysis without the need to export data to another platform.
• Model Deployment: Developers can train and deploy machine learning models directly within SQL Server, making it easier to use these models in production applications.
• Scalability: Since machine learning is performed on the SQL Server instance, it can take advantage of the existing hardware resources, which may lead to improved performance for large datasets.

Example Scenario: A marketing department might use machine learning to predict customer churn. By training a model directly within SQL Server, they can continuously feed it fresh data from their transactional systems, enabling real-time predictions that can drive immediate business actions, such as targeted retention strategies.

3. SQL Server Ledger

Overview: SQL Server Ledger introduces a new feature that provides blockchain-like functionality, enabling developers to build applications with strong data integrity and immutability. This is particularly useful for industries that require stringent audit trails and data verification.

Key Features:

• Immutable Ledger Tables: These tables track all transactions in a way that prevents any modifications after they are committed, creating an audit trail that can be independently verified.
• Cryptographic Verification: Each transaction is cryptographically hashed, and the hashes are stored in the ledger, ensuring the integrity and authenticity of the data over time.
• Integration with Existing Systems: Developers can integrate ledger functionality with existing applications seamlessly, making it easier to adopt this technology without major architectural changes.

Example Scenario: In a healthcare application, patient treatment records can be stored in a ledger table. Each update to a patient’s treatment plan is recorded in a way that cannot be altered, providing a verifiable history that meets regulatory compliance and can be audited as needed.

4. Enhancements to Query Store

Overview: The Query Store feature is designed to help developers manage query performance by providing historical data on query execution, plan choices, and runtime statistics. The enhancements in SQL Server 2022 provide deeper insights and improved capabilities for tracking query performance over time.

Key Features:

• Query Performance Insights: The enhancements include improved reporting capabilities, allowing developers to visualize query performance trends over time and quickly identify any regressions.
• Automatic Plan Correction: SQL Server can automatically identify when a query's performance has degraded and revert to a previously optimal execution plan, reducing the need for manual intervention.
• Improved Visibility into Query Execution: Developers can see detailed information about query execution times, resource consumption, and plan changes, making it easier to diagnose performance issues.

Example Scenario: A web application might experience slowdowns after a new feature is deployed. Developers can use the Query Store to identify which queries have started to perform poorly, understand the reason behind the changes, and either optimise the queries or revert to a better-performing plan.

5. Azure Synapse Link for SQL Server

Overview: Azure Synapse Link provides a seamless integration between on-premises SQL Server databases and Azure Synapse Analytics, enabling developers to perform analytics on operational data without the need to move data to the cloud.

Key Features:

• Near Real-Time Analytics: Developers can run analytics on data as it is ingested, allowing for timely insights that can influence business decisions.
• Simplified Data Pipelines: This integration allows for the creation of streamlined data pipelines, enabling developers to focus on analysis rather than data movement.
• Enhanced Reporting: Azure Synapse provides powerful analytical capabilities that can be directly applied to operational data, enhancing reporting and decision-making.

Example Scenario: An e-commerce company can use Azure Synapse Link to analyse customer behaviour in real-time. By connecting their on-premises SQL Server to Azure Synapse, they can quickly generate reports on customer purchasing patterns, allowing for immediate marketing adjustments based on current trends.

6. Enhanced Security Features

Overview: SQL Server 2022 introduces several new security enhancements designed to protect sensitive data and improve overall database security posture.

Key Features:

• Always Encrypted with Secure Enclaves: This enhancement allows certain operations to be performed on encrypted data without exposing it in plaintext, enhancing the security of sensitive information.
• Dynamic Data Masking Enhancements: These improvements offer developers more options for controlling how sensitive data is presented to users, allowing for greater flexibility in displaying data according to user roles.
• Security Monitoring Tools: New tools provide real-time alerts and recommendations for improving database security, helping developers maintain compliance with best practices.

Example Scenario: In a banking application, sensitive information such as customer account details can be protected using Always Encrypted. Developers can configure dynamic data masking to ensure that only authorised personnel can view sensitive data, thus enhancing customer trust and compliance with regulations.

7. SQL Server Management Studio (SSMS) Enhancements

Overview: The latest version of SQL Server Management Studio includes significant enhancements designed to improve the developer experience, making it easier to manage and develop SQL Server databases.

Key Features:

• Improved IntelliSense: Enhanced IntelliSense capabilities help developers write SQL queries more efficiently by providing better auto-completion and suggestions.
• Integrated Monitoring Tools: New monitoring features allow developers to track database performance metrics directly within SSMS, enabling faster troubleshooting and optimisation.
• User-Friendly Interface: A more intuitive user interface makes it easier for developers to navigate complex database structures and management tasks.

Example Scenario: A developer working on a complex SQL query can rely on the improved IntelliSense features to quickly find the right syntax and suggestions, reducing errors and speeding up the query development process.

8. Support for UTF-8 Encoding

Overview: With the introduction of UTF-8 encoding support, SQL Server 2022 allows developers to store and manipulate multilingual data more effectively, which is crucial for global applications.

Key Features:

• Flexible Data Storage: Developers can specify UTF-8 encoding when creating string columns, enabling efficient storage of various character sets.
• Reduced Storage Requirements: By using UTF-8 encoding, developers can save space compared to UTF-16 encoding, particularly for texts primarily in English or other Latin-based languages.

That's all for now.

New Features in SQL Server 2022 for DBAs

SQL Server 2022 brings a wealth of new features that empower DBAs to enhance database performance, security, and usability. By leveraging these capabilities, organizations can optimise their data management strategies, ensuring better compliance, improved efficiency, and advanced analytics capabilities. The introduction of intelligent query processing, enhanced security features, and support for serverless architectures positions SQL Server 2022 as a robust choice for modern data solutions.

Here’s a summary of the key features and their benefits:

1. Intelligent Query Processing Enhancements

Overview: SQL Server 2022 expands on the Intelligent Query Processing (IQP) capabilities introduced in SQL Server 2019. New features include:

• Parameter Sensitive Plan Optimization: This feature allows SQL Server to create multiple execution plans for a single query based on the specific parameter values used during execution, leading to better performance.

Case Scenario: In a scenario where a stored procedure is called with highly variable input parameters (e.g., a sales report for different regions), the parameter-sensitive optimisation ensures that SQL Server selects the best execution plan tailored to the input, reducing execution time and resource consumption.

2. SQL Server Ledger

Overview: The SQL Server Ledger feature provides blockchain-like capabilities, ensuring data integrity and immutability through ledger tables. This feature uses cryptographic methods to verify data integrity, making it ideal for applications requiring audit trails.

Case Scenario: For financial institutions that need to maintain accurate records of transactions, SQL Server Ledger enables the creation of a ledger table where each transaction is logged. Any modifications are tracked, ensuring an immutable record that can be audited for compliance purposes.

3. Enhanced Security Features

Overview: SQL Server 2022 includes several security enhancements:

• Always Encrypted with Secure Enclaves: This allows more operations to be performed on encrypted data without exposing it in plaintext, enhancing security for sensitive information.
• Dynamic Data Masking Enhancements: Provides more flexible masking options, allowing DBAs to control how sensitive data is displayed to different users.

Case Scenario: In a healthcare application, patient data can be stored securely using Always Encrypted. The application can perform queries and calculations on encrypted data without revealing sensitive information to unauthorised users.

4. Query Store Enhancements

Overview: The Query Store feature has been enhanced to provide deeper insights into query performance over time. It now includes capabilities for identifying and managing query performance regressions.

Case Scenario: DBAs can utilise the enhanced Query Store to track performance changes after application deployments. If a new release introduces slow-running queries, the Query Store can help identify regressions, allowing quick remediation by reverting to previously optimised execution plans.

5. Built-in Machine Learning Services

Overview: SQL Server 2022 integrates built-in support for machine learning services, allowing DBAs to run R and Python scripts directly in the database engine. This feature enables data scientists and analysts to perform advanced analytics without moving data outside the SQL Server environment.

Case Scenario: A retail company can analyse customer purchasing patterns using machine learning algorithms stored in SQL Server. DBAs can schedule these analyses to run during off-peak hours, optimizing resource usage while delivering timely insights.

6. Serverless SQL Database

Overview: SQL Server 2022 introduces serverless capabilities for databases, allowing automatic scaling and cost-effective resource usage based on demand. This feature is ideal for workloads that experience variable usage patterns.

Case Scenario: A startup that experiences fluctuating traffic on its web application can benefit from serverless SQL databases, which automatically scale up during peak traffic and scale down during low usage periods, ensuring cost-efficiency.

7. Enhanced Availability Groups

Overview: New features for Always On Availability Groups improve failover performance and management. This includes automatic failover for databases in availability groups without the need for manual intervention.

Case Scenario: In a mission-critical application, the DBA can configure availability groups with automatic failover capabilities, ensuring minimal downtime during server maintenance or unexpected failures, thus enhancing application reliability.

8. Improved Performance Insights and Monitoring Tools

Overview: SQL Server 2022 provides enhanced monitoring tools with better insights into system performance. Features like the Database Health Monitor offer real-time health checks and performance tuning recommendations.

Case Scenario: DBAs can utilize the Database Health Monitor to receive alerts and recommendations for performance tuning based on workload analysis, enabling proactive management and optimization of database performance.

9. Support for JSON and XML Enhancements

Overview: SQL Server 2022 introduces improvements to support for JSON and XML data types, including better indexing and querying capabilities.

Case Scenario: A content management system that relies on JSON documents can leverage the new indexing features to improve query performance on JSON fields, allowing faster data retrieval and reporting.

That's all for now.

Knowing and Protecting Your Data

Computer circuitry has crept into nearly everything we use nowadays, and almost all of which gather information from us and about us. As a result, the present is immersed in copious amounts of data, which is stored somewhere and, most of the time, not securely protected. 

Today we are almost entirely reliant on IT departments at work; practically all organisations depend on enterprise-wide applications to support numerous key business processes which create a mountain of information. Needless to say, IT infrastructure and business applications are increasingly — and usually, unnecessarily — more complex; complexity is the worst enemy of security — and sometimes, of performance too. As a consequence, we are losing more control of storage and, therefore, security. The more data we share, the bigger the security risk is. 

Companies always strive to successfully harness the power of data, but unfortunately, this is not the case when it comes to securely accessing data. There are laws about protecting information, but most people are either blissfully unaware of them or careless about them. Many of us know what could happen when our data end up in the wrong hands.

Cryptography is Everywhere

Historically, cryptography was used for secret communication by exclusive sectors only — such as governments, military and spies — since it was crucial and affordable to them. They have long been aware of the consequences of their messages falling into the wrong hands; therefore, this situation has motivated the development of techniques for disguising a message so that only the intended recipient can read it. 

The huge desire for secrecy led nations, kings, and queens to make all-out efforts to ensure the security of communications by inventing the best possible secret codes and ciphers. 

A lover in Victorian times

As the public also became aware of the need to protect personal messages of a highly sensitive nature, they also became comfortable with encipherment. They began to express their cryptographic skills in a variety of ways — for example, young lovers in Victorian England were often forbidden from publicly expressing their affection, and could not even communicate by letter in case their parents intercepted and read the contents. This resulted in lovers sending encrypted messages to each other via the personal columns of newspapers, more specifically, via the classified ads. 

Do You Want to Be a Cryptographer?

Alan Turing

I've always been interested in information security since 2003, but it wasn't until I enrolled on the cryptography module — while studying for a Master in Advanced Computer Science in England in 2018— that I started getting more keen on the mathematical side of cryptography; perhaps it was in part because I cherish mathematics and had the right cryptography teacher. What's more, I admire Alan Turing since he strove to do good work in difficult conditions during World War II; his work saved millions of lives. All of these things together inspired me to immerse myself in the world of cryptography. 

Altering SQL Jobs without granting SysAdmin privilege

What would you do if you were asked to grant a few users the permission for altering SQL Jobs? It is a tricky task to carry out although many of us would think that granting SQLAgentOperatorRole role might be enough, however, many years ago no sooner had I done it than I realised it did not work as expected, and now I think most of us faced up this issue at first.
The SQLAgentOperatorRole role allows user to alter SQL jobs as long as the user is the owner of the SQL job, otherwise, the user need to be SysAdmin at SQL instance level. What’s more, one of the best security practices says that nobody but DBA must be SysAdmin, and we should use Windows Authentication. Nevertheless, when it comes to owners for SQL jobs they should use 'sa' as owner which does not mean having enable that account, it should be disable. In this sense, it is highly advisable to have the disabled account “sa” as the owner of all SQL jobs and avoid granting SysAdmin privilege. So, it is of paramount importance not to use a windows user as owner of a SQL job because SQL Server will always validate windows users against the Active Directory and it is likely to get unforeseen errors during that process. 
Today I am going to share with you a stored procedure to enable users to alter SQL Jobs without the need of granting SysAdmin privilege. This stored procedure consists of a logic that will allow a specific user to take the ownership of a SQL Job so that the user can be able to alter it and after making the changes the user can change the ownership to ‘sa’ (or the original owner). This is the stored procedure that I mentioned above.

USE [msdb]
GO
CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max)
with execute as owner
as
begin
    declare @old_owner varchar(max)
    select @old_owner= s.name from msdb.dbo.sysjobs j inner join sys.server_principals s
    on j.owner_sid= s.sid where j.name=@jobname

    --declare @newowner varchar(max)
    --set @newowner=ORIGINAL_LOGIN()
    EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner

    --print @newowner
    declare @msg varchar(max)
    set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + ''''
    print @msg    
end

So, for instance, if you have a SQL job whose owner is 'sa' and your user is 'User2', you will not be able to alter the job until you take the ownership of it temporarily. Here are all the steps you must follow:

1. Create the stored procedure 'usp_change_owner_job' based on the code above.
2. Grant EXECUTE permission on 'usp_change_owner_job' to 'User2'.
3. Grant SQLAgentOperatorRole role to 'User2'.
4. Change the ownership of the SQL Job to 'User2' by using the stored procedure 'usp_change_owner_job'.

EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'

The owner of BusinessJob01 job was changed from 'sa' to 'User2'

5. Now the User2 has the ownership of the SQL job and is now able to alter it.
6. After making the changes on the SQL Job, the 'User2' must change the ownership to 'sa'.

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

The owner of BusinessJob01 job was changed from 'User2' to 'sa'

Having successfully completed all the steps will you be able to alter any SQL Job without granting SysAdmin privilege to users. That is all for now. Let me know any remarks you may have.

Ports and Protocols Used by Microsoft SQL Server

Naturally, I have been asked many times about ports used by SQL Server services and to be honest sometimes I took me some time to reply because there is a great number of ports and protocols and it is not easy to remember them quickly. Not all of us have to learn everything by rote, so thinking about it, I made the decision of sharing the following lists of useful ports and protocols so that you can have them at hand when needed.

Ports and Protocols Used by Microsoft SQL Server 2000
Service / Purpose	Protocol	Port
Analysis Services	TCP	2725
Client connections when "hide server" option enabled	TCP	2433
Clients using Named Pipes over Netbios	TCP	139/445
Microsoft SQL Monitor port	UDP	1434
OLAP Services connections from downlevel clients OLAP Services 7.0	TCP	2393/2394
SQL over TCP **	TCP	1433
Standard URL for a report server (Reporting Services)	TCP	80 HTTP /443 SSL
Ports and Protocols Used by Microsoft SQL Server 2005
Service / Purpose	Protocol	Port
Analysis Services connections via HTTP (default)	TCP	80
Analysis Services connections via HTTPS (default)	TCP	443
Clients using Named Pipes over Netbios	TCP	137/138/139/445
Dedicated Administrator Connection	TCP	1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Reporting services on Windows 2003/2008/Vista (default)	TCP	80
Reporting services on Windows XP SP2	TCP	8080
SQL Server 2005 Analysis Services	TCP	2383
SQL Server Browser Service	TCP	2382
SQL Server Integration Services (MSDTSServer)	TCP	135
SQL Server Resolution Protocol	TCP	1434
SQL over TCP (default instance)	TCP	1433
SQL over TCP (named instances)	TCP	1434 / 1954
Ports and Protocols Used by Microsoft SQL Server 2008/2012/2014/2016/2017
Service / Purpose	Protocol	Port
Analysis Services connections via HTTP (default)	TCP	80
Analysis Services connections via HTTPS (default)	TCP	443
Clustering	UDP	135
Clustering	TCP	135 (RPC) / 3343 (Cluster Network Driver) / 445 SMB / 139 NetBIOS / 5000-5099 (RPC) / 8011-8031 (RPC)
Database Mirroring	TCP	There is no default port for this service. Use the following T-SQL statements to identify which ports are in use: SELECT name, port FROM sys.tcp_endpoints.
Dedicated Administrator Connection	TCP	1434 by default (local port). But this port is assigned dynamically by SQL Server during startup.
Filestream	TCP	139 y 445
Microsoft Distributed Transaction Coordinator (MS DTC)	TCP	135
Reporting services Web Services	TCP	80
Reporting Services configured for use through HTTPS	TCP	1433
Service Broker	TCP	4022
SQL Server Analysis Services	TCP	2382 (SQL Server Browser Services for SSAS port)
		2383 (Clusters will listen only on this port)
SQL Server Browser Service (Database Engine)	UDP	1434. Might be required when using named instances.
SQL Server Browser Service	TCP	2382
SQL Server default instance running over an HTTPS endpoint.	TCP	443
SQL Server Instance (Database Engine) running over an HTTP endpoint.	TCP	80 y 443 (SSL)
SQL Server Integration Services	TCP	135 (DCOM)
SQL over TCP (default instance)	TCP	1433
Transact-SQL Debugger	TCP	135
Windows Management Instrumentation	TCP	135 (DCOM)

That is all for now. I hope you find this post useful. Let me know any remarks you may have. Stay tuned.

Error: could not obtain information about Windows NT group/user

Without any doubt, at times there is a need to use extended stored procedures in SQL Server, for instance, the following error may be raised while using 'xp_logininfo' to get information about domain account 'MyDomainMyAccount' (the account you are logged within SQL Server). The error may also appear when a SQL job, whose owner is a windows account, tries to authenticate against the Active Directory(AD) and this validation fails because of internal security reasons between SQL Server and the AD.

Error: 15404, State: 19. Could not obtain information about Windows NT group/user ‘MyDomainMyAccount’, error code 0x5.

In order to solve this error your Network Administrator has to enable ‘Allowed to authenticate’ security setting on the domain controllers computer object for the account 'MyDomainMyAccount' in the domain 'MyDomain' by following these steps:

1. logon to the Domain Controller of domain 'MyDomain'
2. open Active Directory Users and Computers (dsa.msc)
3. enable the ‘Advanced Features’ under the menu ‘View’
4. navigate to the domain controllers computer object and open the property window
5. click on the security tab
6. add the SQL Service account 'MyDomainMyAccount' and enable the setting ‘Allowed to authenticate’
7. click OK to close the window
8. repeat steps 4-7 on each Domain Controller computer object

Having done that, 'xp_logininfo' will run successfully bringing the information from the Active Directory. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

ObjectExplorer error: Dedicated administrator connections are not supported

Dedicated Administrator Connection (DAC) is a type of privileged connection that we can establish to SQL Server database engine when it does not respond because of too much workload. In this context, DAC is truly useful which allow to diagnosis and solve this sort of problems without restarting the database server any longer. Its is said that it is not possible to establish a DAC connection to the Object Explorer panel via SSMS so that we can get the following error while trying do it in that way:

   Cannot connect to admin:<SQLInstanceFullName>.
   Dedicated administrator connections are not supported. (ObjectExplorer)



It is true up to a point, but it does not mean that SSMS does not support DAC connections. Therefore, the question is:  can we ONLY establish a DAC connection to SQL Server by using SQLCMD? the answer is 'NO'. We also can do it via SSMS while creating a new SQL Query, which means that instead of clicking on 'Object Explorer' and 'Connect', we must click on 'New Query' and finally write 'admin:<Full Name of SQL Instance>' on 'Server Name' text box. That is all, it couldn't have been simpler.



Having done that, we will have a DAC connection ready via SSMS. Let me know any remarks you may have. Thanks for reading. Stay tuned.

How to create new Logins and Users for an AlwaysOn Availability Group

(This tip also applies for Log Shipping and Database Mirroring)

Surely, after setting up an AlwaysOn Availability Group (AG) we are going to need to create additional Logins and Users who must have access and the same permissions across the replica servers so that users can work on each server properly. At times it may end up being a confusing task as SQL Logins may not have matched to the correct SQL Users and therefore users will only access to the SQL instance but not databases in the Secondary Replicas. Do not forget that we cannot create Users directly in Secondary Replicas as they are only read-only, read intent or no access.

To begin with, we need to understand some basic concepts. Logins and Users in SQL Server are different things. Logins allow to access only to the SQL instance and perform tasks at that level such as create/alter/drop databases, alter configurations of the SQL instance, etc. whereas Users allow to get into the databases and work inside them doing delete/update/insert data, create/alter/drop tables, stored procedures, triggers, etc. So, traditionally we always need to have a Login and a User matched each other so as to have access to databases.(Since SQL Server 2012 it is possible to create SQL User without a SQL Login but this aspect is not covered in this post.)

In this context, when it comes to creating new Logins and Users not only will we need to create them in the Primary Replica, but also we have to make sure they also are created in each Secondary Replica so that Users and Applications can log on successfully with the same permissions as they have in the Primary Replica. So we might get some big issues if they are not matched correctly.

Obviously, we need to create the new Login at Primary Replica level and then give the permissions inside the databases. Consequently, ONLY the user will be replicated immediately to each Secondary Replica. So, because the Login is NOT a database object it will not be replicated (it is a server object), which means that we need to create the Login in each Secondary Replica server manually. If the Login is a Windows Login then all what we need to do is to create that Windows Login in each Secondary Replica. A Windows login only has a SID across the whole domain so it will be automatically matched to its Windows User with the same name in each database in the Secondary Replica server. We do not need to know its SID before creating it.

USE [master]
GO
CREATE LOGIN [DOMAIN\percy.reyes] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Now when it comes to working with SQL Logins and SQL Users we must figure out and take the correct SID of the SQL Login in order to use it to create the same SQL Login in each Secondary Replica server. Therefore, we can query the SID of the SQL Login we need by executing the following code like this.

Having got it, we can use it to create the SQL Login by indicating the correct SID. (Do not forget to use the same password and other properties too.)

USE [master]
GO
CREATE LOGIN [preyes] WITH  PASSWORD=N'P4s$Word&1', SID= 0x8AA780458D0F9E4F95D4204B8E022C83, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

In conclusion, only after successfully following this technique will the SQL Login match automatically to the correct user inside the database. It also means that the user will be able to log on any Secondary Replica server with the right permissions inside the databases. That is all for now. Let me know any remarks you may have. Thanks for reading.

How to query the windows users that access via windows groups from SQL Server

At times while monitoring a database server, we may need to know not only which SQL users are accessing, but also windows users for security and auditing purposes. Frankly, this task may be quite simple to get it completed when all of those users are created and visible inside the database engine. Despite this, it is not a surprise that windows users can access via windows groups which means that DBAs can not see them from SQL Server easily unless we have the manner to list them. Luckily, there is an extended stored procedure named “xp_logininfo” that we need to use to get that information. (The 'xp_logininfo' asks the Active Directory for the windows users.)
First of all, this system stored procedure takes two input parameters. The first one is the windows group name, and the second one is the value ‘members’.  For instance, it lists the windows users that access via the windows group ' MyDomain\SQLProdUsrs'. (You need to have “sysadmin” role to execute it.)

EXEC xp_logininfo  @acctname ='MyDomain\SQLPrdUsrs',  @option='members' 

Naturally, this is perfect if we only have one windows group to query. As time goes by, we are likely to get created more windows groups inside the database engine and in no time we will need to create an specific script to figure out the other windows users. Here is that code.

SET NOCOUNT ON

CREATE TABLE #WindowGroup(

    server_name varchar(100),

    account_name varchar(300),

    type char(8),

    privilege char(9) ,

    mapped_login_name varchar(300) ,

    permission_pathsysname  varchar(300)

 )

DECLARE @WindowGroupName varchar(max)

DECLARE @db [NCHAR](128)  

DECLARE cursor_WG CURSOR FOR SELECT [name] FROM sys.server_principals WHERE TYPE='G' 

OPEN cursor_WG

    FETCH NEXT FROM  cursor_WG INTO @WindowGroupName

    WHILE @@FETCH_STATUS= 0

        BEGIN

            INSERT #WindowGroup(account_name , type , privilege  ,mapped_login_name  ,permission_pathsysname   )

            EXEC xp_logininfo  @acctname =@WindowGroupName  ,  @option =  'members' 

        FETCH NEXT FROM cursor_WG INTO @WindowGroupName

    END 

CLOSE cursor_WG

DEALLOCATE cursor_WG

UPDATE #WindowGroup SET server_name=@@servername

select server_name AS ServerName, account_name as WindowsAccountName, Type, Privilege, mapped_login_name as MappedLoginName, permission_pathsysname as WindowsGroupName

FROM #WindowGroup 

ORDER BY permission_pathsysname,mapped_login_name

DROP TABLE #WindowGroup

SET NOCOUNT OFF

As you have seen, the T-SQL code is simply easy to understand. Clearly, it filters the windows groups by indicating the value ‘G’ for the Type column of the system view “sys.server_principals”, and eventually through the cursor each of them is read. Now I hope you make the most out of this script to have better visibility of the windows users accessing the databases. Thanks for reading again!

AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed

While working on a heap of High Availability and Disaster Recovery solutions, I was challenged to deal with some complex errors that I had to overcome as fast as possible. Today’s post is going to show how we may solve one of them. I am speaking about the following error which is raised when we have to set up the AlwaysOn AG Listener:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. 
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

At times it can be quite easy to fix it, but it may become complicated as we do not have more details of the root cause, therefore, it does not give us any clue. In my experience working on this, the most common cause has to be about lacking of permission for the cluster name account so we have to make sure that this account has the 'Create Computer' and 'Read' permissions:



Once you have given right permissions and if the error is still there then you must check whether the IP Address is available to be assigned to the AlwaysOn AG Listener. It is simple to verify by making ping to IP Address which should be free. If not, ask your Administrator a new IP Address and try again.
I hope this practical post helps you. Let me know any remarks you may have. Until next post, thanks for reading!