Stairway to SQL Server Security Level 2: Authentication (2021-03-17)

 SQLServerCentral Newsletter for March 17, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Security Level 2: Authentication]( - [Encrypt and Decrypt Passwords in SQL Server with PowerShell]( - [Unlocking the “PASS Pro” Educational series]( - [From the SQL Server Central Blogs - How I tested Parse data transformation in Azure Data Factory]( - [From the SQL Server Central Blogs - ADF: Error trying to debug pipeline: BadRequest]( Question of the Day - [Which place]( The Voice of the DBA  Daily Coping Tip Get outside and notice how the weather feels on your face I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Every IT Organization is a Snowflake I was listening to a presentation on IT organizations and how they can change and improve in the future. At one point the speaker said that "every IT organization is a snowflake." Is this true? Do you think that each place you've worked is completely unique? Certainly the names of servers, IP addresses, and plenty of technical details differ. The internal applications are coded differently, colors, shapes, UX, all could vary. But do you think that your IT group is a [snowflake]( That term in some sense means unique, but in a derogatory way. It is used to represent people who are easily offended by something different. While I've not always been pleased with the way an IT department is structured or managed, I'm not sure I've been offended. I do think that every organization has some assembled its staff, process, technology, and more into a unique set of resources. However, I also think there are many commonalities between how the departments are run. After all, managers change jobs as often as technical people, and they will bring their processes and habits with them. While every situation is unique, I don't know I think that many IT departments are snowflakes. As I work with customers, both in development and operations, I find many of them fall into a few broad categories in how they run their business. If you disagree, leave me a comment today. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Security]( [Stairway to SQL Server Security Level 2: Authentication]( Don Kiely from SQLServerCentral.com Authentication is the first step in letting a principal get access to an instance of SQL Server, essentially resolving the question, Who are you? In this stairway level you’ll learn about the basics of authentication and the authentication options available. This level covers logins and users and you’ll learn about the password policies that can help strengthen SQL Server authentication. [Encrypt and Decrypt Passwords in SQL Server with PowerShell]( Additional Articles from MSSQLTips.com In this article we look at how to securely store data in text files that could be used for SQL Server authentication or wherever you need to keep data secure. [Unlocking the “PASS Pro” Educational series]( Additional Articles from Redgate Thanks to the consent of the speakers, the expert-led educational training courses covering the following four topics: Data Modernization, Azure Migration, Azure Synapse, Azure SQL, are now available to all. You can watch the sessions and learn from a range of experts online, for free. From the SQL Server Central Blogs - [How I tested Parse data transformation in Azure Data Factory]( Rayis Imayev from Data Adventures (2021-Mar-15) While watching the first MiB movie with my family, it was quite clear that the character played by Will Smith at first didn’t have a clear idea about what... From the SQL Server Central Blogs - [ADF: Error trying to debug pipeline: BadRequest]( Ed Elliott from Ed Elliott's Sql Developer Blog I made a mistake recently when I was creating an ADF pipeline, annoyingly I made loads of changes and then clicked the debug button, when I pressed debug the...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Which place I have this code in R: which(letters =='z') What does this return? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The CTE Scan I have this code to create and query a CTE on the AdventureWorks2017 database: WITH Customers AS ( SELECT CustomerID FROM Sales.Customer) SELECT * FROM Customers UNION ALL SELECT * FROM Customers; How many times is the Sales.Customer table scanned? Answer: twice Explanation: The Sales.Customer table is scanned twice. You can check this with STATISTICS IO with a query of the table and then the CTE query, which will show 37 and 74 logical reads, respectively. You can also run this and look at the query plan, which should be similar to this: [CTE Execution plan] Ref: Common Table Expression Misunderstandings - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [problem Deadoolk in my instance]( - Good morning all I reset a deadlook problem every 10 minute Who can help me please optimize the execution of these requests  Please find attached the execution plan of my request select FMENV.ENVIDX as IDX, 3 as Entity, FMBATCH.BATLOGNAME as FM_Batch, FMENV.ENVIDX as FM_Document, FMENV.ENVLOGNAME as Name, FMENTPROP.EPRCUST19 as FM_ID_Client, FMENTPROP.EPRCUST09 as FM_Checking, FMCATEGORY.CATDESC […] [Connecting to SQL 2017 with SQL Server Driver from Windows 2012R2]( - Hi we have a legacy application that connects from the application servers (Windows 2012 R2) to the SQL servers using the SQL Server driver. This work fine against an old version of SQL, but we're upgrading to SQL 2017, and it no longer connects. You can connect from Windows 10 using the SQL server driver. […] [Most accessed tables/indexes]( - This is a generic question to know more about SQL server performance.Nobody has complained of any performace issues on our servers, but there is an agenda to know the existing performance and how that can be improved upon. What are the metrics that need to be captured to identify a baseline for a sql server […] SQL Server 2017 - Development [GROUP BY with SELECT SUM]( - Why is this not working? Field 'EmpNo' not contained in either an aggregate function or the GroupBy clause SELECT EmpNo, SUM(Time1) AS TotalTime, Dept, Job, Description, Rework FROM TimeData2 GROUP BY EmpNo SQL Server 2016 - Administration [sql 2016 alwayson question about failover]( - Hi guys, i have this question. we have 2 sql 2016 standard with some databases replicated with basic alwayson. So last night the primary server crashed (BSOD and reboot) and the secondary sql has started the failover. This morning i have done a manual failover to move again the DB on primary server. i have […] [SQL Server FC 2016 on Windows 2016 - Random Cluster Failure]( - Hi, For about a week my cluster is failing. It starts with event log: and immediately: After that is down the drain. One of the nodes freezes, SQL instances/roles are green but inaccessible. The server isn't releasing them unless, I just turn it off. This is when the roles migrate. It is not hardware as […] [Licence SQL server Edition developper sous une machine EC2]( - Good morning all Can I take advantage of the fact that the SQL develop license is free to install it on EC2 AWS machine?Is the use of SQL developing ON EC2 AWS free thank you for your clarification Administration - SQL Server 2014 [Shutdown order for nodes in AG and quorum disk]( - When shutting down the whole farm environment with two nodes in Failover Cluster/AG, my thinking is to shutdown secondary first and then primary. When bringing up, start with primary and then the secondary. What I noted was when the secondary was brought up first and then the primary, the databases synced and AG dashboard showed […] Development - SQL Server 2014 [How to Structure Data table/fields when using Multiple Select Drop Down]( - Hello, I am interested in using a multi-select drop down on an application interface and am looking to understand how to handle this at the database level to (1) Store the data, (2) Query the data. Below is an example of the concept. I have a drop down with multiple check boxes that asks, "Please […] [Sql query - Projected Monthly Revenue]( - Hi, I've been sitting with a projected revenue query for sometime , trying to figure out the best way to do this. Basically, I'm trying to get a monthly revenue number based on daily shipment totals. eg: Total Rev MTD $88,720 / 9 days =  $9,857 * 23 shipping days = $226,731 as Monthly Project […] [Clone a table from a script]( - I have a table schema in an exported .sql file (text). Is there any way to use that to create an identical table (with the name changed of course)? The file was created using script table as -> create to -> file. TIA  SQL Server 2019 - Administration [Encrypt/Decrypt issue]( - Version :Microsoft SQL Server 2019 (RTM-GDR) Followed these links as underneath: I created 2 tables. For the first table I inserted individual rows (with encryption as in the links) and when I did a decrypted select.The result came out perfectly.No issues.The result showed the original password column,encrypted password column and then the decrypted […] SQL Server 2019 - Development [Help with non-clustered index fragmentation]( - I'm trying to rebuild indexes to clear some heavy fragmentation in the nonclustered indexes. I've tried ALTER TABLE dbo.POHeader REBUILD and ALTER INDEX idxPOHeader_EntryDate ON dbo.POHeader REBUILD Neither affects the index fragmentation. What is the correct method to resolve this.  Reporting Services [Printing from generic SSRS report]( - I have a generic SSRS report that contains some text and bar codes. I wish to print it but when I try to export it to PDF the bar codes are replaced by the strings they represent. When I right click on the report and try to print, I get all of the surrounding info […] Integration Services [VS 2015 deploy to SQL 2019]( - Hi We just had a SQL upgrade (SQL 2019) in our dev server and our SSIS solutions no longer work. We are using Visual Studio 2015 and the solutions run fine locally from within VS. Is there a compatibility issue between VS2015 and SQL2019? I can't find much on the web. In VS, the solution […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -