Determine if SQL login is using Windows Authentication or SQL Authentication

Windows Authentication

SELECT * FROM sys.sysusers WHERE isntuser = 1

SQL Authentication

SELECT * FROM sys.sysusers WHERE issqluser = 1

Recently I had a recurring issue where a user was being inexplicably dropped from the a SQL Server database.

I used this query to quickly identify the user/application that was removing the user accounts:

DECLARE @traceLog VARCHAR(256)

SELECT @traceLog = CAST(value AS VARCHAR(256)) 
FROM  ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
  AND property = 2

SELECT TextData,
       NTUserName,
       HostName,
       ClientProcessID,
       ApplicationName,
       LoginName,
       EventClass,
       TargetUserName,
       StartTime 
FROM ::fn_trace_gettable(@traceLog, DEFAULT) traceLog 
INNER JOIN sys.trace_events traceEvents 
ON traceLog.EventClass = traceEvents.trace_event_id
WHERE traceLog.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)
ORDER BY traceLog.StartTime

While setting up a SQL AlwaysOn Availability Group I ran into a very interesting problem.

I had created the AG while preparing for an upcoming speaking engagement so I’d setup two VMs (Node A and Node B) and checkpointed them immediately before creating the AG. This way I could just go back to the checkpoint and start the demo during my presentation.

This turned out to be very helpful because for the very first time I ran into an issue setting up a SQL AlwaysOn Availability Group. All previous configurations had gone flawlessly. After a lot of head scratching and reverting the VMs to try again; the only error I could get from the AG Dashboard is: “This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.” Node B would simply NOT connect to the AG.

Not very helpful.

While searching around for possible solutions I stumbled upon this article discussing troubleshooting SQL AlwaysOn Availability Group joining issues. The author provided a very handy SQL statement to get some additional details about the Availability Group:

--Run below command to check endpoint state 
select r.replica_server_name, 
       r.endpoint_url,
       rs.connected_state_desc, 
       rs.last_connect_error_description, 
       rs.last_connect_error_number, rs.last_connect_error_timestamp 

from   sys.dm_hadr_availability_replica_states rs 

join   sys.availability_replicas r

on     rs.replica_id=r.replica_id

where  rs.is_local=1

Per the authors instructions I run on both nodes. When run against the problematic Node B I received this:

An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.

Further searching based on this error didn’t turn up much related to SQL Availability Groups, but I did find this article discussing the same issue with Database Mirroring. The author suggests there are issues when using the default Local System SQL server service account.

I reverted to my checkpoint and changed the service account to a domain account PRIOR to configuring the AG. The AG configuration completed successfully and the AG shows no issues.

Typically it’s unnecessary to adjust the default SSRS email report settings, but sometimes it’s unavoidable. For instance, in our case we needed to expose the SSRS web portal on a specific URL.

We’re using the latest version of SSRS: SQL Server SSRS 2016

I found several references and guides on how to change this via the SSRS configuration file:

{MSSQL DIR}\MSRS13.MSSQLSERVER\Reporting Services\ReportServer

All the guides correctly identified the <UrlRoot> node. However, what was not made clear was what value to use.

After some trial and error the solution was:

http://www.yourdomain.com/ReportServer

Restarting the SSRS services is required after making this update.

Quick way to copy the table schema using SQL Server Management Studio:

1. Right-Click table to copy
2. Script table as
3. CREATE To
4. New Query Editor Window
5. Find & Replace all instances of the table you are copying with the table you wish to create

NOTE: Pay attention to any Primary Key and Foreign Key indexes listed in the SQL script. They may require updating as well.

Went digging around for ways to view the active connections in Microsoft SQL 2008. To my surprise Microsoft moved the Activity Monitor. Apparently they did this way back in SQL 2005. I skipped 2005 so I missed that. To view the open connection:

1. Open Management Studio.
2. On the standard toolbar is a little graph icon. That is the Activity Monitor (CTRL + ALT + A).
3. Click on Processes

Simple as that!

Thanks to Tim for reminding me what a headache this can be!

I’m guilty of creating SQL statements like so:

SELECT testfield FROM sometable

Today that caused a real problem. An error that was incredibly difficult to find.

The error message was:

11 >= 11 null

Seriously, that was the error message displayed by Coldfusion.

The answer was that we should’ve qualified the table name like so:

SELECT testfield FROM [sometable]

You can take it a step further and do this for your columns as well. I usually do not qualify my column names unless it’s necessary, but qualifying your table name is easy and may save your hair.

I ran into a problem trying to return the newly created identity from a table. The identity column was a uniqueidentifier type.

Table structure:

I needed to retrieve the new UniqueID value after an INSERT statement.

The default value for UniqueID is set to newid().

Here is my original code:

<cfquery name="insert" datasource="#DSN#">
INSERT INTO foo (textfield) VALUES ('test1')
SELECT SCOPE_IDENTITY() AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>

The newid variable is always an empty string.

I also tried:

<cfquery name="insert" datasource="#DSN#">
INSERT INTO foo (textfield) VALUES ('test1')
SELECT @@IDENTITY AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>

No luck. After a little searching I found the solution. SQL Server doesn’t support returning a uniqueidentifier using the traditional methods.

New code:

<cfquery name="insert" datasource="#DSN#">
DECLARE @NewUniqueID uniqueidentifier
SET @NewUniqueID = NEWID()

INSERT INTO foo (uniqueid,textfield) VALUES (@NewUniqueID,'test1')
SELECT @NewUniqueID AS NewUniqueID
</cfquery>

<cfset newid = insert.NewUniqueID>