Voice of the DBA

Tag: SQLNewBlogger

Changing the sa Password with SQLCMD

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

SQLCMD is a command line tool, so open a command prompt.

Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

Once you’ve connected, you can issue this code:

ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

Once I’ve done this, test the sa login from a new session and verify it works.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

References

SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

April 22, 2016
Basic SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

I then typed SQLCMD. After a delay, I got this:

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

If I enter go, I’ll get this:

Not so easy to read. I have to scroll up to even figure out what the display is:

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

April 14, 2016
Changing the sa Password with SSMS–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

Changing sa with SSMS is really simple. Follow these steps:

Connect to SSMS as a sysadmin. You can check this for your login. Then expand the Security folder and the Logins folder. Right click the “sa” account and choose properties.

Once you do this, you’ll get the login properties dialog, and see the Password text field at the top in the General tab.

You can type a new password in the Password box, and confirm this in the Confirm Password box. Password policies checks are up to you, though I’d recommend you use them.

Click OK and the password is changed. You can then connect a new query window as “sa” and verify your password.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

April 11, 2016
#SQLNewBlogger–Adding Local Accounts
What do you do if you need a process running under Local Service to connect to your SQL Server? Most of the advice out there is to change the login account. I actually agree with that, but there are times you can’t, or don’t want to.

There are certainly times when I’ve seen some automated process use one of these accounts:
- NT Authority\Network Service
- NT Authority\Local Server
Often this is because someone doesn’t want to bother to learn how to enable other accounts for their application, which isn’t a good excuse. In my case, I had a local VSTS agent service running as part of a demo, where I had very limited rights. I couldn’t affect a change, and I needed to get a new login for SQL Server.

I searched a bit, but most advice said to just change the account, after all, if you had a process connecting from another machine, Local Service won’t work. However I found one item on Stack Overflow that helped.

Here’s my Login list. As you can see, I have Network Service, but not Local Service.

I the run this code:

CREATE LOGIN [NT AUTHORITY\LOCAL SERVICE] FROM WINDOWS;

This gives me a new login.

In my situation, I then had to add this to the dbcreator role, but I could treat this like any other login and assign the minimum privileges needed.

SQLNewBlogger

I had to solve this and decided to write about it. The writing took 10 minutes, the research was 15-20 minutes to find a good reference and experiment a bit.

A good learning exercise, and all of you should know how to do this. Prove it with your own blog.
April 4, 2016