How Lakehouse Architecture is Revolutionizing B usiness Intelligence (2023-05-27 DBW)

Validating Password Expiration

I would guess that the majority of instances I've had to manage in my career were those that I didn't initially install and configure. I've inherited more instances than I would bother to count, and I often need to double-check what's been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven't always been overly concerned about checking: password expiration.

There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I've seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It's not.

I don't know that I've run queries to check the value in the is_expiration_checked column is appropriately set. If it's not, then Steve's post above will help you change those logins. That's a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

Ideally, no one would ever create logins without expiration set, but sometimes things happen. I've seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

Steve Jones - SSC Editor

Join the debate, and respond to the editorial on the forums

If you are using SSDT for authoring, building, debugging, and publishing a database project, how do you change to, or preferably migrate towards, a Flyway-based database development? Flyway doesn't need to replace any code part of SSDT, but if allowed to manage every release candidate, it does allow for much cleaner branching, merging, and deployments.

Meta has built AI models that can recognize and produce speech for more than 1,000 languages—a tenfold increase on what’s currently available. It’s a significant step toward preserving languages...

Today, we got a new service request with the follo...

DBCC OPENTRAN is a DBCC command in Microsoft SQL S...

Do you ever have a stored procedure that you know ...

In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have... The...

Our customer reported today that Data Sync failed is failing with the following inner exception: System.OutOfMemoryException error. Sync failed with the exception 'An unexpected error occurred when applying batch file...

Today, we got a error message while trying to enable cdc for a database using the sqladmin user. Our customer got the error message: Msg 22830, Level 16, State...

Choosing the right database monitoring tool can be quite challenging, considering the multitude of options available. Right Database Monitoring Tool First appeared on How to Choose the Right Database Monitoring...

Recently I was doing a cleanup of one model, and I used Best Practice Analyzer (BPA) in Tabular Editor as a helper. Later, I realized I forgot to set...

Recently I was doing a cleanup of one model, and I used Best Practice Analyzer (BPA) in Tabular Editor as a helper. Later, I realized I forgot to set... The...

Vector search in Azure Cosmos DB for MongoDB vCore enhances AI-driven applications using OpenAI API or custom-built solutions by incorporating vector similarity search into your database.

Get hands on with Spark SQL (no Python or Scala) to build your first data pipeline. In this video I walk you through how to read, transform, and write the NYC Taxi dataset with Spark SQL.

Today, we faced a service request where our custom...

With User Managed Identity support for SQL Managed Instance, Auditing to Storage Account target can be configured with the following authentication methods for SQL MI: Managed Identity Storage Access Keys Managed Identity can...

Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with... The...

I was asked a thought-provoking question: "If I could time travel, what would I have wished to know when starting with databases?" First appeared on Time Machine Advice: What I...

Reorganizations and layoffs are now commonplace in the tech industry: a daily occurrence. It’s increasingly necessary for tech workers mentally prepare for potential job loss and uncertainties that arise....

Activate reservation utilization alerts and take control of your Azure reservation strategy.

Is your team looking to modernize and migrate your data but have questions about the complex organizational challenges? This webinar panel on June 29th covers all aspects of data modernization and migration that senior IT leaders should be aware of, including organization culture, communication, and politics. Why not send it to your boss?

In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post to learn more.

Fun With PowerShell - Opening Websites with Start-Process

Digital procedures play an important role in modern business, as they generate lots of valuable information that can be used to improve organizations and advance their goals. Thus, in...

Simon Rowe gives a multi-pie chart a once-over: Th...

When choosing a particular graph type, there are no hard and fast rules. With so many options available, it can be tempting to select a visual that you’ve used...

I ran across an interesting post from Rita Fainshtein that looked at the different types of graphs for a set of data. I thought that was interesting, so I... The...

The Lakehouse is the evolution of the earlier cloud data platform in many pieces that came with "some assembly required". All of the components are modern, mature and capable...

Microsoft Fabric comes with many objects that can ...

Years ago, DCAC put a bunch of equipment in a Colo (and did a massive server upgrade last year). The facility we’re in is massive taking up an entire...

Intel announced details of its Falcon Shore refocus and announced that it had delivered 10,000 blades for the Aurora supercomputer.

The start of this week sees a return to form with price drops across the board for a lot of Gen 4 and Gen 3 M.2 SSDs.

Jeffrey Wang plays matchmaker: In this final insta...

Introduction to dynamic format strings feature released in Power BI May 2023, including a few technical details of its implementation.

Nikola Ilic shares some thoughts: I’ve already ...

At today’s Build conference, Microsoft announced Fabric. What is this? In simple terms, think of taking Synapse Analytics, Data Warehousing, Data Lakes, Data Factory, Spark Notebooks and Machine Learning,...

You might have heard the news of the announcement at Microsoft Build by Arun Ulag and the rest of the Microsoft Data and Analytics Platform team. The announcement was...

Microsoft has had a strong lead in BI and analytics for a long time now. The introduction of the tabular data models in 2012 was a watershed moment. Today,...

If you have heard about Microsoft Fabric in the Microsoft Build 2023 event’s announcement, Here are explanations for enabling it for your tenant. This setting will be automatically enabled...

Microsoft has just announced Microsoft Fabric during the Microsoft Build conference. This is a unified analytic solution of the era of AI. Microsoft Fabric is an end-to-end, unified analytics... The...

Reading Time: 7 minutes In this post I want to cover spreading your SQL Server wings with Microsoft Fabric . As part of a series of posts about...

Reading Time: 4 minutes In this post I want to cover spreading your SQL Server wings with serverless SQL Pools. As part of a series of posts about...

Welcome back to the MySQL optimization series! In case you haven’t been following this series, in the past couple of articles we have discussed the basics of query optimization,...

SQL Server 2022 Degree of Parallelism Feedback Is ...

Using Statistics Parser to easily read output from STATISTICS IO and STATISTICS TIME. The post Statistics Parser appeared first on SQLServerCentral.

A Little About Why Parallelism Doesn’t Make Every SQL Server Query Faster Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love...

One aspect that can significantly impact performance is scanning the buffer pool in Microsoft SQL Server. This blog explores buffer pool scan. First appeared on SQL Server 2022 Performance –...

A Little About Improving Modification Query Performance In SQL Server Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love my training....

A Little About Date Math In SQL Server Queries Going Further If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m...

Azure DevOps pipeline templates allow you define common functionality once, then reuse it in many pipelines. A great use case for this is publishing #PowerBI reports automatically -- templated...

Microsoft Fabric (#MicrosoftFabric), the new Unified Analytics platform, was announced this morning, literally moments before the Build Conference. Satya made the official announcement in the keynote address. Why is...

I can’t be more excited! After the first time I heard about Microsoft Fabric, it directly took my interest. Especially as I see this as a nice bridge in...

I am super excited to release this blog post and I cannot wait to start using Microsoft Fabric and all the different components. The goal of my blog post...

I just finished writing this book a few weeks ago, and as I read it, I thought “why not review my own book?” Of course, the question that first...

This book will provide you with a good foundation ...

Erin Stellato has an update for us: It seems like ...

New features now available in GA include the ability to enable MLOPs across dev, test, and prod environments to promote assets across multiple workspaces.

Database Migration Assessment for Oracle extension analyzes an organization's Oracle database, identifying performance requirements and code compatibility with the target Azure database for a smooth migration.

Azure Database for MySQL connector for Power Platform, Azure Logic Apps is now available and allows you to connect to any MySQL Flexible Server database to read and write...

An extension that automates and provides code conversion from Oracle to SQL to simplify and accelerate the migration process.

Azure Cosmos DB serverless 1-TB is now generally available. Enjoy expanded storage capacity, allowing you to store up to 1 TB of data in a single container.

Now generally available, hierarchical partition keys in Azure Cosmos DB enables you to partition your data with up to three levels of keys.

With input and output bindings for SQL, you can now can quickly write Azure Functions that read and write from your databases.

Now generally available, Azure Cosmos DB burst capacity helps improve performance by allowing you to use idle throughput to handle temporary spikes in traffic.

Integrate Azure Functions and Azure SQL easily with the Azure SQL bindings for Azure Functions, now generally available. Input and output bindings speed up development time by reducing boilerplate...

Azure Stream Analytics supports for end-to-end exactly once semantics when writing to Azure Data Lake Storage Gen2.

Up to 80% price reduction in Azure Stream Analytics

Learn how to use the Plotly library to visualize time series data in Python in this step-by-step article.

When you make plots with Python’s matplotlib package, the package chooses some default colors for you. In the code below, we draw the lines for . Notice that the...

Today, we got a service request that our customer reported a high CPU usage in Azure SQL Database. Following I would to share with you my lessons learned here. Our...

This is part 3 of an ongoing series on why you should use R. Future blogs will be linked here as they are released.

This is the beginning of a series on SQL Server Dy...

Transparent data encryption is the built-in method for at-rest data encryption in SQL Server. Taking steps towards security has always been important, but it is becoming even more important...

The CHECK_EXPIRATION option is a security feature ...

Learn a way to build referential integrity for tab...

I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a...

This article drills down on how to compute and compare statistics using T-SQL for the arithmetic mean, the geometric mean, and the median.

The walkout is part of a greater wave of anxiety s...

Setting up a local web server was something that I haven’t done in a long time and this was really easy. This post shows how to do this with... The...

GitHub Copilot, an AI-powered pair programmer, is now available for Azure Data Studio to assist you with context-aware code completions, suggestions, and even entire code snippets. Get started today with...

If you’re lucky, you’ve only had to worry about managing a data disaster recovery effort once or twice in your career, if at all. However, as the rate and...