Monday, February 23, 2015

SQL Server Default Trace 0



--To see if default trace is enabled
SELECT* FROM sys.configurations WHERE configuration_id = 1568


--To enable default trace
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO


--To query contents of default trace file
SELECT TE.*
     , T.*
  FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150)
       , ( SELECT TOP 1 f.[value]
             FROM sys.fn_trace_getinfo(NULL) f
            WHERE f.property = 2)), DEFAULT) T
  JOIN sys.trace_events TE 
    ON T.EventClass = TE.trace_event_id


More info:

http://www.databasejournal.com/features/mssql/a-few-cool-things-you-can-identify-using-the-default-trace.html

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

Monday, February 16, 2015

The Target Principal Name Is Incorrect. Cannot Generate SSPI Context

Setting up an instance of SQL Server 2008 R2 64bit Standard, got this error when trying to connect from off the server.

Still working to determine the cause, in the meantime was able to temporarily remedy by disabling names pipes and setting service account to "LocalSystem".

Connection String Variations

.\instance
(local)\instance
127.0.0.1\instance - loopback
servername\instance
ip\instance
ip,port
tcp:servername\instance - tcp/ip
lpc:servername\instance - shared memory
np:servername\instance - named pipes

Saturday, February 7, 2015

SQL Server Network Client Utility 32/64bit

You can set up SQL Server aliases on a Windows Server or PC using the Network Client Utility even if they don't have the SQL Server Client tools installed.

The trick is to keep in mind that there are two different SQL Server Network Client Utility runtimes on a 64-bit machine.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed.

If you are using a 64 bit machine, the default utility will be for 64-bit data sources.

If you have a 64bit OS and are trying to access an alias in your 32bit application and receive the error check to see if you have an alias configured for the architecture of your application ( 32-bit / 64-bit ).

Use this utility to Review 64-Bit aliases and protocols
c:\windows\system32\cliconfg.exe

Use this utility to Review 32-bit aliases and protocols
c:\windows\sysWOW64\cliconfg.exe


Additional info:
http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx

http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

http://www.sharepointassist.com/2010/02/02/configure-a-sql-server-alias-for-sharepoint-sql-server-2008/