One what is DAC
SQL Server 2005 provides an administrator with a special diagnostic connection for use when it is not possible to establish a standard connection with the server. Even when SQL Server does not respond to standard connection requests, administrators can use this connection to access SQL Server to perform diagnostic queries and resolve problems. The command line Interface (SQLCMD) provides and supports this dedicated administrator connection (DAC) by using a special administrator switch (-a).
two how to use the DAC to log on to the server
1 Local DAC Logins
Execute SQLCMD-A-S SQL Server name in command-line mode
2 Remote DAC Login
1 Open the Remote DAC option
' Program '-> ' sql Server2005 '-> ' Configuration tool '-> ' SQL Server peripheral configuration '-> ' feature of the peripheral configuration '-> ' DataBase Engine '-> ' DAC '- > ' Enable remote DAC '
2 Log on to the remote server
Sqlcmd-a-S 192.168.0.1-u sa-p 123456
3 after logging in, you can diagnose problems by querying dynamic management views
1. Use the DAC to connect to the server
In SQL Server Management Studio, do not open the other DAC, click Database Engine query on the toolbar.
In the Connect to Database Engine dialog box, in the Server name box, type ADMIN:, and then continue to type the name of the server instance. For example, to connect to a server instance named Acct\payable, type admin:acct\payable.
Complete the authentication section (provide credentials for members of the sysadmin group), and then click Connect. The connection will be established.
2.DAC settings can be connected,
0-Indicates that only local connections are allowed to use the DAC
1-Indicates allowing remote connections to use the DAC
sp_configure ' remote admin connections ', 1;
Go
Reconfigure;
Go
Execute in Studio DAC mode or in Sqlcmd-a-o c:\1.txt mode, output file
Copy Code code as follows:
DECLARE @return_value int
EXEC @return_value = [dbo]. [Sp_undecrypt]
@procedure = Testencry,
@revfl = 0
SELECT ' return Value ' = @return_value
Go
The decryption stored procedure is as follows:
Copy Code code as follows:
1 Create PROCEDURE [dbo]. [Sp_undecrypt]
2 (@procedure sysname = NULL, @revfl int = 1)
3 AS
4/**//*
5 Wang Chenghui Translation Finishing, paste, please specify from Microsoft BI pioneer www.windbi.com
The 6 invocation form is:
7 exec Dbo.sp__windbi$decrypt @procedure, 0
8 If the second argument uses 1, some hints about the stored procedure are given.
9--version 4.0 correction stored procedure too long to decrypt out is a blank question
*/
SET NOCOUNT on
IF @revfl = 1
BEGIN
PRINT ' WARNING: The stored procedure deletes and rebuilds the original stored procedure. '
PRINT ' Make sure that your database has a backup before running the stored procedure. '
PRINT ' This stored procedure should normally be run in a product environment that is a backup of a non-product environment. '
PRINT ' to run this stored procedure, change the value of the parameter @refl to 0. '
return 0
End
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint, @procNameLength int
Select @maxColID = max (Subobjid) from
Sys.sysobjvalues WHERE ObjID = object_id (@procedure)
--select @maxColID as ' Rows in Sys.sysobjvalues '
Select @procNameLength = datalength (@procedure) + 29
DECLARE @real_01 nvarchar (max)
DECLARE @fake_01 nvarchar (max)
DECLARE @fake_encrypt_01 nvarchar (max)
DECLARE @real_decrypt_01 nvarchar (max), @real_decrypt_01a nvarchar (max)
DECLARE @objtype varchar (2), @ParentName nvarchar (max)
Select @real_decrypt_01a = '
--Extracts the object's type as a stored procedure or function, and if it is a trigger, also gets the name of its parent object
Select @objtype =type, @parentname =object_name (parent_object_id)
From sys.objects where [object_id]=object_id (@procedure)
--a imageval record from sys.sysobjvalues
SET @real_01 = (SELECT top 1 imageval from sys.sysobjvalues WHERE ObjID =
OBJECT_ID (@procedure) and Valclass = 1 ORDER by Subobjid)
--Create a temporary table
CREATE Table #output ([ident] [int] IDENTITY (1, 1) not NULL,
[Real_decrypt] NVARCHAR (MAX))
--Start a transaction and roll back later
BEGIN TRAN
--Change the original stored procedure and replace it with a dash
If @objtype = ' P '
SET @fake_01 = ' ALTER PROCEDURE ' + @procedure + ' with encryption as SELECT 1
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
else if @objtype = ' FN '
SET @fake_01 = ' ALTER FUNCTION ' + @procedure + ' () RETURNS INT with encryption as BEGIN return 1
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + '/End '
else if @objtype = ' V '
SET @fake_01 = ' ALTER view ' + @procedure + ' with encryption as select 1 as Col
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
else if @objtype = ' TR '
SET @fake_01 = ' ALTER trigger ' + @procedure + ' on ' + @parentname + ' and encryption after INSERT as RAISERROR (' N ', 16,10)
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
EXECUTE (@fake_01)
--From the sys.sysobjvalues, to put up an encrypted fake.
SET @fake_encrypt_01 = (SELECT top 1 imageval from sys.sysobjvalues WHERE ObjID =
OBJECT_ID (@procedure) and Valclass = 1 ORDER by Subobjid)
If @objtype = ' P '
SET @fake_01 = ' Create PROCEDURE ' + @procedure + ' with encryption as SELECT 1
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
else if @objtype = ' FN '
SET @fake_01 = ' CREATE FUNCTION ' + @procedure + ' () RETURNS INT with encryption as BEGIN return 1
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + '/End '
else if @objtype = ' V '
SET @fake_01 = ' Create view ' + @procedure + ' with encryption as select 1 as Col
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
else if @objtype = ' TR '
SET @fake_01 = ' Create trigger ' + @procedure + ' on ' + @parentname + ' with encryption after INSERT as RAISERROR (' N ', 16,10)
/**//* ' +replicate (CAST (' * ' as nvarchar (max)), datalength (@real_01)/2-@procNameLength) + ' * *
--Start counting
SET @intProcSpace =1
--Populate temporary variables with characters
SET @real_decrypt_01 = Replicate (cast (' A ' as nvarchar (max)), (Datalength (@real_01)/2))
--loop to set each variable to create a real variable
--one byte at a time
SET @intProcSpace =1
--if necessary, traverse each @real_xx variable and decrypt
While @intProcSpace <= (datalength (@real_01)/2)
BEGIN
--True and false and encrypted false for different or handled
SET @real_decrypt_01 = Stuff (@real_decrypt_01, @intProcSpace, 1,
NCHAR (UNICODE (substring (@real_01, @intProcSpace, 1)) ^
(UNICODE (substring (@fake_01, @intProcSpace, 1)) ^
UNICODE (substring (@fake_encrypt_01, @intProcSpace, 1)))
SET @intProcSpace = @intProcSpace +1
End
--Inserting variables into the table #output via sp_helptext logic
Insert #output (real_decrypt) Select @real_decrypt_01
--select Real_decrypt as ' #output Chek ' from #output--Test
-- -------------------------------------
--Start extracting from sp_helptext
-- -------------------------------------
declare @dbname sysname
, @BlankSpaceAdded int
, @BasePos int
, @CurrentPos int
, @TextLength int
, @LineId int
, @AddOnLen int
, @LFCR INT--The length of the carriage return line
, @DefinedLength int
, @SyscomText nvarchar (max)
, @Line nvarchar (255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0--a space that tracks the end of a line. Note The Len function ignores extra spaces
CREATE TABLE #CommentText
(LineId int
, Text nvarchar (255) collate Database_default)
--using #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR Local
For the SELECT Real_decrypt from #output
ORDER BY ident
For READ only
--Get text
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT from ms_crs_syscom into @SyscomText
While @ @fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN (@SyscomText)
While @CurrentPos!= 0
BEGIN
--Find the end of a row by carriage return
SELECT @CurrentPos = CHARINDEX (char () +char, @SyscomText,
@BasePos)
--If a carriage return is found
IF @CurrentPos!= 0
BEGIN
--If the new value of the length of the @lines is greater than the set, insert @lines current content and continue
while (LEN (@Line), 0) + @BlankSpaceAdded + isnull
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(IsNull (LEN (@Line), 0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
IsNull (@Line, N ") + IsNull (SUBSTRING (@SyscomText,
@BasePos, @AddOnLen), N ')
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
End
SELECT @Line = IsNull (@Line, N ') +
IsNull (SUBSTRING (@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N ')
SELECT @BasePos = @CurrentPos +2
INSERT #CommentText VALUES (@LineId, @Line)
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
End
ELSE
--If you don't find the carriage
BEGIN
IF @BasePos <= @TextLength
BEGIN
--If the new value of the @lines length is greater than the defined length
while (LEN (@Line), 0) + @BlankSpaceAdded + isnull
@TextLength-@BasePos +1) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-
(IsNull (LEN (@Line), 0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
IsNull (@Line, N ") + IsNull (SUBSTRING (@SyscomText,
@BasePos, @AddOnLen), N ')
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
End
SELECT @Line = IsNull (@Line, N ') +
IsNull (SUBSTRING (@SyscomText, @BasePos, @TextLength-@BasePos +1), N ' ")
If LEN (@Line) < @DefinedLength and Charindex (",
@SyscomText, @TextLength +1) > 0
BEGIN
SELECT @Line = @Line + ', @BlankSpaceAdded = 1
End
End
End
End
FETCH NEXT from ms_crs_syscom into @SyscomText
End
IF @Line is not NULL
INSERT #CommentText VALUES (@LineId, @Line)
Select Text from #CommentText ORDER by LineId
Close ms_crs_syscom
Deallocate ms_crs_syscom
DROP TABLE #CommentText
-- -------------------------------------
--End extraction from sp_helptext
-- -------------------------------------
--delete stored procedures created with dashes and rebuild the original stored procedures
ROLLBACK TRAN
DROP TABLE #output
