Creating a System Admin Login for SQL Server Using the Command Line

SQL Server logo

This past week I installed Microsoft SQL Server 2014 Developer Edition on my dev box, and immediately ran into a problem I had never seen before.

I’ve installed various versions of SQL Server countless times, and it is usually a painless procedure. Install the server, run the Management Console, it’s that simple. However, after completing this installation, when I tried to log in to the server using SSMS, I got an error like the one below:

SQL Server login error 18456
“Login failed for user… (Microsoft SQL Server, Error: 18456)”

I’m used to seeing this error if I typed the wrong password when logging in – but that’s only if I’m using mixed mode (Windows and SQL Authentication). In this case, the server was set up with Windows Authentication only, and the user account was my own. I’m still not sure why it didn’t add my user to the SYSADMIN role during setup; perhaps I missed a step and forgot to add it. At any rate, not all hope was lost.

The way to fix this, if you cannot log on with any other account to SQL Server, is to add your network login through a command line interface. For this to work, you need to be an Administrator on Windows for the PC that you’re logged onto.

1. Stop the MSSQL service.

2. Open a Command Prompt using Run As Administrator.

3. Change to the folder that holds the SQL Server EXE file; the default for SQL Server 2014 is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

4. Run the following command: “sqlservr.exe –m”. This will start SQL Server in single-user mode.

5. While leaving this Command Prompt open, open another one, repeating steps 2 and 3.

6. In the second Command Prompt window, run “SQLCMD –S Server_Name\Instance_Name”

In this window, run the following lines, pressing Enter after each one:

>CREATE LOGIN [domainName\loginName] FROM WINDOWS 
>GO 
>SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN' 
>GO

7. Use CTRL+C to end both processes in the Command Prompt windows; you will be prompted to press Y to end the SQL Server process.

8. Restart the MSSQL service.

That’s it! You should now be able to log in using your network login.

Converting a SQL Table to JavaScript Objects Using the ASP.NET MVC Web API

ASP.NET MVC Web API logo

Happy Independence Day!

Here I will describe an alternative to an earlier post that described using a SQL stored procedure to convert a SQL table into a JavaScript object.

One of the comments left on that post made me wonder why someone would not want to use the stored procedure method. After querying a large table with this method, I do understand why it’s not ideal. It takes quite a long time to pull back large quantities of data.

The method described below, using the Newtonsoft JsonWriter class in an ASP.NET MVC Web API, is much faster and does not require any SQL stored procs. The code I used was modified from Hristo‘s answer on Stack Overflow.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Text;
using System.IO;
using Newtonsoft.Json;

namespace RESTfulAPI.Services
{
    public class JsonRepository
    {
        public string GetAllJsons(string tableName)
        {
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            HttpContext context = null;
            String connectionString = string.Empty;
            String json = string.Empty;
            try
            {
            connectionString = ConfigurationManager.ConnectionStrings["/* (your named connection string in web.config) */"].ConnectionString;
            conn = new SqlConnection(connectionString);
            command = new SqlCommand("SELECT * FROM " + tableName, conn);
            context = HttpContext.Current;

            StringBuilder allJSONs = new StringBuilder();
            // ... SQL connection and command set up
            conn.Open();
            command.CommandTimeout = 3600;
            rdr = command.ExecuteReader();

            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            jsonWriter.WriteStartArray();
            while (rdr.Read()) {
                int fieldcount = rdr.FieldCount; // count how many columns are in the row
                object[] values = new object[fieldcount]; // storage for column values
                rdr.GetValues(values); // extract the values in each column
                jsonWriter.WriteStartObject();
                for (int index = 0; index < fieldcount; index++)
                { // iterate through all columns
                    jsonWriter.WritePropertyName(rdr.GetName(index)); // column name
                    jsonWriter.WriteValue(values[index]); // value in column
                }
                jsonWriter.WriteEndObject();
             }
            jsonWriter.WriteEndArray();
            rdr.Close();
            json = sb.ToString();
            //End of method
        }
        catch (SqlException sqlException)
        { // exception
            json = "Connection Exception: " + sqlException.ToString() + "n";
        }
        finally
        {
            conn.Close(); // close the connection
        }

        return json;
        }
    }
}

Creating an ASP.NET MVC 4 RESTful API for a SQL Stored Procedure

ASP.NET MVC Web API logo

In order to use the information from the SQL stored proc I implemented that would create a JSON Object from a SQL table, I needed to build an RESTful API web service that would retrieve the JSON for use elsewhere.

Finding information on building an ASP.NET-based API that used a stored proc to pull the data was not easy, as there were plenty of pages that had part of the info, but none I found had everything I needed. The article “Build RESTful API’s with ASP.NET Web API” was very helpful in giving me a starting point.

One of the things I did was to eliminate the Model (the “M” in MVC) from the site, as I did not want the column name to appear in the string returned by the API.

(“RESTfulAPI” is the name of the namespace for this project.)

The Controller that I created has the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using RESTfulAPI.Services;

namespace RESTfulAPI.Controllers
{
    public class JsonController : ApiController
    {
        private JsonRepository jsonRepository;

        public JsonController()
        {
            this.jsonRepository = new JsonRepository();
        }

        public string Get(string tableName)
        {
            return jsonRepository.GetAllJsons(tableName, null);
        }

        public string Get(string tableName, string department)
        {
            return jsonRepository.GetAllJsons(tableName, department);
        }
    }
}

I created a folder called “Services” at the same level of Controllers and added the following class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace RESTfulAPI.Services
{
    public class JsonRepository
    {
        public string GetAllJsons(string tableName, string department)
        {
            SqlDataReader rdr = null;
            SqlConnection conn = null;
            SqlCommand command = null;
            var connectionString = string.Empty;
            var json = string.Empty;
            connectionString = "Server=.SQLEXPRESS2008;Database=HackHou2008;Integrated Security=SSPI";

            conn = new SqlConnection(connectionString);
            command = new SqlCommand("GetJSON", conn);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@table_name", tableName));
            if (department != null)
            {
                command.Parameters.Add(new SqlParameter("@department", department));
            }

            conn.Open();
            rdr = command.ExecuteReader();

            while (rdr.Read())
            {
                json += rdr["json"];
            }
            return json;
        }
    }
}

When the website is installed, the API can be accessed at “(site home)/API/json?tablename=tablename”. If you add other parameters to the stored proc, they will need to be added in the Get() method above, and the URL calling the API will have those parameters appended using ampersands (i.e., “(site home)/API/json?tablename=tablename&parameter2=parameter2&parameter3=parameter3”, etc.).