Leaning all things SQL Server and PowerShell
I have been working with SSDT the past few months at my day job, in order to get a database versioned. I am also trying to use it for a few things with a side job as well.
So, a simple task to start with is create a new SQL Database Project, and import a script (.sql) file to bring in all the database objects. You would think it was simple until you go through the process, get no errors and no objects in your project.
Mother Hubbard, what the heck is the matter? No errors, and the summary log does not give any help either. Well, I got curious so I opened up the script in Visual Studio and received this message:
Select “Yes”, and then save the file. Repeat the steps above and voila it works, at least for me. You will also notice it took a good bit more time to complete.
Finally purchasing a laptop, I ended up passing the deadline to get one loaded with Windows 7. So I have been working on Windows 8 now for about 3 or 4 months probably.
I have an HP Pavilion g6-2238dx, the prime reason I picked this particular model was price at Best Buy and it had a quad-core processor. It came with 4GB of RAM and have since moved this up to 8GB.
Overall Windows 8 is not that bad. It is a bit more drastic of a change than people may have experienced between Windows XP and Vista. With a laptop, at least with HP, there are gestures you can use on the mouse pad. Which at times this gets annoying depending on how you are used to use a mouse pad. It does take getting used to.
The biggest thing I hear about is that Microsoft took away the famed “start” button that showed up on the bottom left of the screen. Which it does not just show up on the task bar anymore but it did not go away. You just have to put your mouse at the bottom left corner to make it appear, then click.
The start menu itself though, is a bit annoying. As well are the “apps” that Microsoft put that initially tries to take over playing music, video, or viewing of pictures. On my laptop they just sit there and I end up changing the default program to open the particular file types. I wish they would work on that though, it could be improved with some minor changes I am sure.
Most applications I run have not had any problems running on Windows 8. I have a few that I use for clients (VPN software and such) that I chose to build a Windows 7 virtual machine (using VMPlayer), instead of cluttering up my laptop with it.
Summary, just give it time.
UPDATE
Ok, after giving it about 3 months…
Windows 8 has two “modes” of applications: Desktop and Tile (I think it is called Tile). The desktop mode applications you interact just like Windows 7. With the tile apps you are going through the new Start Menu. These application modes are take a bit to get used to. I chose to pretty much remove them. You will have some things like opening PDF files that the default program is set to the PDF Reader Tile app. With my laptop they are slow to load and you cannot interact with other applications very easily, at least I can’t figure out how to. One good example is the Kindle app for Windows 8. I have SQL Server books and tend to go between reading and then doing the example on my VM. With the Tile Application mode there is no way of easily doing this. SO I had to remove it and go find the Windows 7 desktop version of the Kindle Reader. You will find, at least so far I have, that most of the applications have a “desktop” version. There are a few that I had to dig around the Internet to find it.
The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.
Here’s an excerpt:
600 people reached the top of Mt. Everest in 2012. This blog got about 2,000 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 3 years to get that many views.
I was tooling around in SSMS the other day. In this particular instance I was granting a login instance-level permissions to test out a third party application at work. I did not want to give it sysadmin privileges for this so I just started out with giving it VIEW SERVER STATE (which most DMVs require in order to query them) and VIEW ANY DATABASE. [If you have never heard of these permissions, check out this little tidbit of info here from Brian Kelley (b|t).)
Wait…go and actually read the tidbit of info from Brian...
Ok now that you have read that, you saw the T-SQL code to find the server-level permissions granted to any account on an instance of SQL Server 2005 or 2008. Well what if I only wanted it for one login and I wanted to use the GUI? Ah, ha!!!
So on my test system at home I have [myUser], with the current permissions showing below (retrieved using Brian’s T-SQL code).
Now I am going to grant the account VIEW SERVER STATE and VIEW ANY DATABASE. Running the T-SQL code again will show you this:
Now how can you see this using SSMS, you ask? …I’m glad you asked. Open up SSMS and go to the properties of your login you are interested in, click on “Securables” in the left pane. You should see this window:
Now click on the “Search” button. You are presented with options as to what objects you want SSMS to search for, I chose “The server…”. You can play around later to see what the options do if you like, these selections are not remembered so once you close the window they go away. After clicking OK you will see your instance name show up under Securables and the bottom pain will show all the instance level permissions. I scroll all the way to the bottom and will see the permissions I assigned to my login:
That is all for now…
A loyal guide
Fair warning that some religous terminology may be mentioned in this blog post, continue reading at your own risk.
I recently took part in the first round of the Mentoring Experiment. This is a program that Steve Jones (t|b) and Andy Warren (t|b) started (they seem to do that alot).
I acted as the mentoree and had someone mentor me. Overall it was a great experience. I want to send my thanks out to Steve and Andy for allowing me to participate. The plan of the was for me and my mentor to schedule a few phones calls each month to talk about different things. We talked about where I wanted my career to go, and where I was at currently. I got some good advice from it on how to grow my knowledge and get more involved with things. I greatly appreciate my mentor sharing that time away from his family each month during this program trial.
I would advise anyone interested in SQL Server, especially if you are just starting out with it, to watch the Mentoring website I linked to above for the second cycle to start up. It can help you get your plan written out to where you want to go with your career. Having someone that has been through it already is a great way for you to learn what things work and what does not.
The main thing I have learned is that I need to build those skillsets that best match up to what I want to do. If I don’t then I won’t go any further than where I am at right now. I have the belief that God has a plan for me and my career. What the end result will be I do not know and don’t want to know, the fun part is the journey.
Short and sweet post here…not the sugar sweet, the awesome sweet!!!
So I’m still working on the script I talked about in a previous post. I wanted to try to drop using the parameter and try to work it all out in the script if it comes across more than one instance.
So I came up with this, initially:
$regInstance = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
if ($regInstance.Count > 1)
{
"More than one instance found on server."
foreach ($ins in $regInstance)
{
$regPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$ins
$fullpath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$regpath"
}
}
else
{
$regPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$ins
$fullpath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$regpath"
}
So that was nice, but you probably already noticed an issue? The way it is written the $fullpath will only have the last value it hits in the foreach loop, if more than one SQL instance exist. That will not work. So where do we turn…hash tables. How you might ask? While we pause for station identification, go read this post from The Scripting Guy! Blog.
The example in that post that helped me was adding 100 intergers as key/value pairs in a hash table. I could capture both the InstalledInstances and the Instance Names values, and keep them associated with each other in a hash table. This improves the options I have of reusing this snippet in other scripts possibly. (One example comes to mind is being able to pass the instance name in SMO scripts.)
So the 18 lines of code from above, turned into this:
#create hash table
$hashInst = @{}
#populate hash table with values
$inst = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $inst)
{
$hashInst.Add($i,(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i)
}
A total of just 6 lines of code for readability, could go down to 3 if you put the foreach on one line.
You find out PowerShell is fun when you start playing with it and actually try to perform a task with it. I have been using it the past few months to try and decrease the time it takes me to perform a scan of a SQL Server. What I mean by scan is I work with the IASE Security Technical Implementation Guide checklist (STIG). You can find the checklist for SQL Server here, they have not published one for SQL 2008 so I just use the SQL 2005 checklist for now.
Anyway some of the findings in that checklist have you check configuration settings that could be checked through SSMS or through registry keys. I prefer to check registry keys or use SQL SMO if I can, cause guess what…PowerShell can do it for me.
I am not going to provide the complete script right now cause it is still a work in progress. I have a parameter required to pass with the script, the instance name. I was just using that to connect with SQL SMO, but now I have just figured out how I can use that with looking at the registry.
With a SQL Server installs (SQL 2005 and higher) it adds a registry hive of “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server”. To really get deeper than that you have to know what the path is for the instance and it varies between SQL Server versions and default/named instances. In SQL Server 2005 it is MSSQL.# (# being a number between 1 – 9). With SQL Server 2008 it changed to MSSQL10.<InstanceName> and SQL 2008 R2 it is MSSQL10_50.<Instance Name>. So if you were working with a default instance the path for SQL Server 2008 and R2 would be MSSQL10.MSSQLSERVER or MSSQL10_50.MSSQLSERVER. With SQL Server 2005 it would be your best guess cause if there are multiple instances it will depend on the order it was installed. If the default instance was first it would be MSSQL.1, then any named instance after that would have that number incremented.
Needless to say it took me a few minutes to get it down right but this is the code I came up with, and there is probably another way of doing it (or even doing it with less). I have not tested it on every version of SQL Server. If I come across any bugs I will update this post.
Note: $InstnaceName is the variable used to pull in the parameter passed when calling the script.
I was pretty excited that work was finally going to install Remote Desktop Connection Manager v2.2 on our laptops, until I started using it. It is nice if you work on one server at a time but sometimes I multitask while something is running, or I need to compare something to the results on another server. You can “undock” the window and then go to full screen like you would with the regular Remote Desktop Connection, however you have to do that for each server and each time after closing it.
So I decided to go back to RDP files, using Remote Desktop Connection. I did not want to go through the process of having to create those files all over again. So guess what I wanted to do? That’s right, see if PowerShell could do it for me.
A quick search on Google I came across a post from the Windows PowerShell Blog on RDP file generation. The post is actually a review of a script someone wrote and blogged about that will create multiple RDP files with PowerShell. Which the original author of this script is lost since the link to the author of the script is dead.
Anyway, I decided to start with this script but do a few things differently. As well, the PowerShell Team mentioned one of the flaws in the guy’s script is it is dependent upon a CSV file, something developers may call “hard coded”. I wanted to make it more portable and useful for other folks to use.
My script assumes the current directory so where you run the script from is where the files will be created. I did not care about creating directories or creating RDP file with different resolution settings. If you do, you are more than welcome to adjust the script to your preferences.
The script has three (3) parameters to pass, with two being required and the third one is optional. The first position is the $server that you want to connect to in the RDP file. The second being your RDP file template (I’ll explain how I created mine). Then the third and optional parameter, is the username for that RDP file. I don’t usually put my username in the RDP file due to the environment I work in, but you may want to.
Ok, first how I created the RDP template file. I open up Remote Desktop Connection (from the run prompt: mstsc.exe). Go into the options and set everything as you like (Display, Local Resources, etc.). Then under the General tab click “Save As…”. Save the file to your desktop or where you saved the script, giving it a name of your liking. Then using Notepad open that file up and you will see something similar to this:
The highlighted line is what you want to remove and then save the file. This line will be added by the script and include your server name you pass.
So here is the script…(download here)
param(
[Parameter(Position=0,Mandatory=$True)]
[string]$server,
[Parameter(Position=1,Mandatory=$True)]
[string]$MyDefaultRDP,
[Parameter(Position=2,Mandatory=$False)]
[string]$myaccount
)ForEach($entry in $server)
{
#build the file
#assumes current directory
$filename = “.\” + $entry + “.rdp”# Add hostname in RDP file
$temp = “full address:s:” + $Entry#test for username, if empty it will not be added
if ($myaccount) {
$temp = $temp + “`nusername:s:” + $myaccount
}
#check to see if file exist
if (Test-Path $filename) {
Remove-Item $filename -force
Write-Host “Found $filename existed, so I deleted it for you”
}$temp | Out-File $filename
Get-Content $MyDefaultRDP | Out-File $filename -Append
Write-Host “$filename created”
}
[Anyone know an easy way to format PowerShell code? This is the best I could get with Windows Live Writer Add-On.]
Ok so here are some examples of how you can use this:
If you need to use an IP address for your server instead of the hostname:
Then if you want to add your username:
If you want to use a text file to pass a list of servers and different usernames I would suggest using a CSV file. Then work with the Import-Csv cmdlet to pass in each object to the right parameter.
Something like this should work for you (assume column headers in CSV file are Server and Username):
NOTE: A small issue I have had running this script on my machine at home (Window 7 Ultimate) is the escape character “`n” (backtick + n) is supposed to add a new line before it writes the username text to the file. However it is not doing it when I run the script. If I have the $temp output to the console it shows the new line, but when it writes it to the file it does not for some reason. Which with the RDP file it does not matter as you can see in the screenshot above it still puts the username in the field. I don’t know if you will see the same problem, but wanted to let you know.
So I got a new server given to me to check over. Unlike most folks I like to open up PowerShell to get quick fix on the configuration. I seem to have gotten used to using PowerShell enough now that it takes me about as much time to do it PowerShell as it would in SSMS. So I like hitting the keyboard more than I do clicking the mouse.
It is a Window Server 2008 box and SQL Server 2008 R2 so I know PowerShell is available to me for my perusing.
Open up PowerShell firing off a few commands as such:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' MyInstance
So good so far. I then go to check a few things to find out what the build number is and what the directory path is for the instance.
$s.Information.version
This will give you the Major, Minor, Build, and Revision information of that instance. It returns that no issue. However the next command returns nothing:
$s.Information.RootDirectory
What this should have given me is the path to the files for that instance on the server. The \MSSQL10_50.InstanceName\MSSQL\ path. However all it did was send me back to a prompt, without any output or error message. So I decided to just look at all of the information to see if it returned anything.
$s.Information
Which that line returns an error that looked like this:
format-default : An exception occurred while executing a Transact-SQL statement or batch.
+ CategoryInfo : NotSpecified: (:) [format-default], ExecutionFailureException
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ExecutionFailureException,Microsoft...
Which did not really help me, but I had an idea it had something to do with my permissions not being setup correctly. So I took that CategoryInfo line and popped it into the mighty Oracle (aka Google). The first link that came up was a blog post from 2009 by Michiel Wories on how to get more detailed error information from PowerShell. Which stemmed from a blog post Allen White (b|t) had written on actually how to handle errors in PowerShell.
Apparently the message outputted to the console does not include all of the error. There is an “InnerException” that has all the meat of the error. To get that you can execute the command below. Now I believe you can only do this if you are working interactively with the console. If you are running a script you would need to check Allen’s post to see how you can capture the error itself when your script is executed.
$error[0] | Format-List InnerException -Force
From the output of this command I see the rest of the story:
An exception occurred while executing a Transact-SQL statement or batch. ---> The EXECUTE permission was denied on the ojbect 'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permissions was denied...on and on.
Each object that should have been returned from executing $s.Information shows the “EXECUTE permission denied” message for that object. Which by itself is cool because you see what is happening in the background, and I now know what is being executed to return that information to me.
So now you know how to get to this information when you get an error in PowerShell that just may not give you enough info to go on. I am now off to get my permissions fixed.
Source: Flickr
If you set the service account for the SQL Server services up at installation you are ahead of the game when dealing with securing your SQL Server installation. However there are some environments where the DBA does not do the initial installation of SQL Server. That requires the DBA to go back and configure a custom account for the services after the installation.
In most instances if you use the SQL Server Configuration Manager (SSCM) to set the service account it should configure all the file, registry, and operating system permissions SQL Server needs for you. See this TechNet article on SSCM.
A question to ask is do you ever go back and periodically check to see what operating system permission the account was actually given? Do you check to make sure someone has not dorked around with the permissions and made your SQL Server instance not come back up at next reboot?
Well you can do this by opening up the group policy editor on the server and go through each permission SQL Server is supposed to have. However, you would have to go through all of the OS permissions to make sure the service account has not been granted excessive permissions to the operating system.
Well who wants to use the GUI when we can use PowerShell!!! So here is a nice little one-liner I use to verify what permissions the SQL Server service account(s) have on a server:
Get-WMIObject -Namespace root\rsop\computer -Class RSOP_UserPrivilegeRight -Recurse | Where-Object {$_.AccountList -like "*SQL*"} | Format-Table UserRight -AutoSize
This portion of the code is where you will adjust it for your environment: “{$_.AccountList -like “*SQL*”}”. As written above this will return the permissions found to have an account name/group assigned to it that contains the text “SQL” within the name. Adjust this portion to your specific requirements.
This is a screenshot of what your output should resemble. (Do you see the excessive or un-needed privilege returned in the output?):
A little bit of a caveat for you on this code though is that it will only work on domain member servers. For some reason, that I never bothered to look up, the RSOP namespace is not available or accessible on stand-alone servers.
