Welcome to EMC Consulting Blogs

The full error message is SQL Server Error 18452, "Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication" 

I have just got this error message again on a Windows 7 Workstation, that I am using SQL Server Management Studio on.

It reminded me that I wanted to blog about the resolution to this and also add how to solve this for Named Instances as well.

Firstly some background to the issue:

I have a Windows 7 Workstation that I use SQL Server Management Studio to connect to SQL Servers in both a Production (PROD) and TEST Domains. However, I log on to the Workstation using a Local Windows Account. I have to do this as the PROD and Test Domains are predominantly XP, and the Group Policy that was developed for XP is different to that used in Vista and Windows 7. Currently, we are not able to operate in a mixed mode domain as a result the current roaming profiles we have DO NOT WORK for Window 7. This is a major problem because it means whenever you log off you have to re-assign Outlook Exchange connections and you lose all your internet favourites etc.

So I have 3 Accounts all with the same Username

LOCAL\MyUser

PROD\MyUser

TEST\MyUser

I log onto my Windows WorkStation using LOCAL\MyUser and then want to connect seamlessly to both PROD and TEST SQL Servers using SQL Management Studio using Windows Authentication (Not SQL Authentication). I can either do one of three things:

1) I can attempt to use the Shift Key and then Right Click on SQL Management Studio and 'Run as Different User' and use either PROD or TEST Accounts

However, I cannot then intermix in SSMS Local SQL Server and different Domain SQL Servers - I am stuck with just those SQL Servers in the Domain I logged in on.....

 2) I can attempt to  use a Line Command as detailed by Jason Follas here:

http://www.jasonfollas.com/wiki/(X(1)S(zucey4jfr2sr4maocepgrn55))/Default.aspx?Page=SQL%20Management%20Studio%20on%20Vista&AspxAutoDetectCookieSupport=1

However, again I am limited to using just those SQL Servers in the one Domain, and secondly also I am prompted for the password each time as /savecred does not work with /netonly at the same time.

3) Finally I can attempt to use Windows Credential Manager to store the various Domain Credentials for me. However, when I did this (the first time) it did not work and I still had the SQL Server Error 18452 Message. I could not find out the answer until Google popped up the following link and Arsine's answer (thanks again Arsine!)

http://social.msdn.microsoft.com/Forums/hu-HU/sqlsecurity/thread/c05a90e4-cb16-46f6-9072-37083c65696d

I have to create a Windows Credential and add the SQL Server Port (1443) to the end of the Internet or Network Address in the form :<SQL_Port_Number>

So to seamlessly connect to SQL Servers in Any Domain from Windows 7 in SQL Management Studio you need to
 
•         Use Control Panel, User Accounts, Manage Your Credentials, Add a Windows Credential
 
•         Add all the SQL Servers you want by Fully Qualified Name but you must include the PORT Number as say  :1433
 
•         Example:
 
•         ANYSERVER.ANYCOM.TEST:1433
 
•         Username: TEST\AnyUser 
•         Password: TEST Password
 
•         Press Add Button
 
Note: Each SQL Server will have to be added separately
 
Note: If there are any Password changes then all references in Windows Credentials will need to be updated

This way you can control all SQL Servers in ALL Domains seamlessly from one instance of SQL Server Management Studio
 
I would like to add two additional points to Arsine's excellent posting

A) I find that it is best to Fully Qualified Name the ServerName in Windows Credentials and SQL Management Studio rather than just the Servername . So use ANYSERVER.ANYCOM.TEST rather than just ANYSERVER

B) If you have a NAMED INSTANCE of SQL Server you must create multiple entries in Windows Credentials, one using the default SQL Server Port 1433 and one for each Port allocated for the Named Instance such as 2335 so use

ANYSERVER.ANYCOM.TEST:1433

as well as  

ANYSERVER.ANYCOM.TEST:2335

Note: Of course this will have to be defined as ANYSERVER.ANYCOM.TEST\NamedInstanceName in SQL Management Studio

To find out the Port that SQL is running on (there are many methods) I suggest you read the link below by Colin Stasiuk which contains by far the most comprehensive solutions I can find. I didn't like hunting throug Registry Entries and looking at the SQL Server Logs was by far the quickest solution (noting the 'possible gotcha' detailed )

http://sqlserverpedia.com/blog/sql-server-bloggers/what-tcp-port-is-sql-server-running-under/

Finally I know I could have used SQL Server Authentication without a problem to achive the same thing but I did not want to go through this less secure method.

PS. To avoid having to use the Shift Key when Right-Clicking for 'Run As Different User" in Windows 7:

• Just go to registry run regedit.exe, search for “runasuser”, then under it you’ll find empty value REG_SZ named “Extended”.
•  Delete every “Extended” (there should be around 5 of them
• Result is that there is no need to press shift key anymore, the option will ALWAYS appear when you Right-Click

My Project Manager asked me to find out 'How many lines of code have we in the System'. I could not
believe that no one has ever had to do this before so I googled 'sql server stored procedure number of lines' and found
the following posting from Gordon Bell (many thanks!) on Stack Overflow in November 2008. See link below

http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-pro

However, the solution Gordon provided did not allow you to pass in a DatabaseName as a Parameter (and I had to find this
out for all the databases in a system) - I could have run it in each database individually but I thought I would take
the base code and allow a DatabaseName to be passed in as a parameter.

Furthermore, I thought I would update the code to use the latest SQL sys.objects and sys.sql_modules system tables
rather than older sysobjects ans syscomments system tables.

Additionally, I thought I would add a Grouping Parameter, so you can return either the

1) Total Lines of Code for each Stored Procedure and each Function for any Database. GroupingType = 'N' - Default
2) Total Lines of Code for all Stored Procedures and all Functions for any Database. GroupingType = 'T' - Default
3) Total Lines of Code for all Stored Procedures and Functions for any Database . GroupingType = 'D'

Finally as this is written using Dynamic SQL I have a Switches for Print SQL (Debug) and Execute SQL

Note: The code is not perfect because it counts the number if newlines and you could have a number of these with no text on them and these will be added to the count line total. Therefore you need to eliminate any lines with just whitespace from them to get the "real" line count value. Perhaps I will have a go at doing this in the future.

The code to run this is below 

and it is called via 

EXEC dbo.esp_GetObjectNumberOfRows

@DatabaseName = 'master',

@GroupingType = 'N',

@ExecuteSQL = 1,

@PrintSQL = 0;

CREATE PROCEDURE dbo.esp_GetObjectNumberOfRows

@DatabaseName SYSNAME = '',

@GroupingType NCHAR (1) = 'N', -- N = Object Name, T = Object Type, D = Database

@ExecuteSQL BIT = 1, -- Default Execute SQL On

@PrintSQL BIT = 0 -- Default Debug SQL Off

BEGIN;

IF @DatabaseName = ''

SET @DatabaseName = DB_NAME(); -- Default is current database

DECLARE @SQLMessage NVARCHAR (MAX);

SET @SQLMessage = '

' + NCHAR (39) + @DatabaseName + NCHAR (39) + ' AS DatabaseName,

' +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN '

END AS ObjectCount,' ELSE 'SUM(T.ProcedureCount) + SUM(T.FunctionCount) AS ObjectCount, ' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription, ' ELSE '' END + '

FROM ' + @DatabaseName + '.sys.objects o

INNER JOIN ' + @DatabaseName + '.sys.sql_modules c

' +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'GROUP BY ' ELSE '' END +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription' ELSE '' END + '

ORDER BY 1' + CASE WHEN @GroupingType IN ('N', 'T') THEN ', T.ObjectDescription DESC' ELSE '' END

+ CASE WHEN @GroupingType IN ('N') THEN ', T.ObjectName ASC' ELSE '' END + ';';

IF @PrintSQL = 1

PRINT @SQLMessage;

IF @ExecuteSQL = 1

EXEC (@SQLMessage);

END;

Last week I was looking at a performance issue with a piece of code that had never been identified as having an issue before. In these cases you say 'whats changed' well the answer in this case was nothing with the code.. But a lot with the data we were running through it.. Basically because we were running a one-off data clean up exercise though the code we were processing thousands of rows per run rather than tens or rows all this in SQL Server 2008

Anyway, because we are highly trained IT professionals we tried the data fix in our test lab first, and that's where we found that the code while finishing OK was hanging at one step.. The actual step was found using the code that Merrick Chaffer has made available here:

 http://merrickchaffer.blogspot.com/2011/07/currently-executing-sql-view.html

 Anyway, it was a basically an Insert into a temporary table which had an OR in the join logic for one table.. I will not use the actual code but the issue can be illustrated with the example below

INSERT INTO #Temp_Update
   (
   TABLE_B_ID,
   TABLE_B_Column
   )
SELECT  B.TABLE_B_ID, 
   B.TABLE_B_Column
FROM  dbo.TABLE_A   A
INNER JOIN dbo.TABLE_C   B
ON   A.TABLE_A_ID        = B.TABLE_A_FK_ID
INNER JOIN dbo.TABLE_C   C   
ON  (
  B.TABLE_B_ID        = C.TABLE_B_FK_1_ID
OR  B.TABLE_B_ID        = C.TABLE_B_FK_2_ID
  )
WHERE A.TABLE_A_Column       = 1 
AND  (
  C.TABLE_B_FK_1_ID       IS NOT NULL
OR  C.TABLE_B_FK_2_ID       IS NOT NULL
  );

On its own with no much data throughput on  driver TABLE_A when the join was very quick.. When we had thousands on the driver TABLE A the querty took an age to resolve even with ZERO rows to return. Bascially because the Query Parser could not resolve the OR statement well enough it was generating a complex execution plan with very poor execution time.. 

I was chewing the cud with this with a colleague Gareth Keen, and he said.. I have seen an article somewhere where an complex OR join was replaced with a UNION ALL statement... "but I don't think you can use it in this case'.. I said I maybe, because all I am actually doing at a lower level is testing that the data 'exists and I can replace the last join with an EXISTS LEFT SEMI JOIN (which is can be faster anyway). To see what this would look like I have converted the above..

 INSERT INTO #Temp_Update
   (
   TABLE_B_ID,
   TABLE_B_Column
   )
SELECT  B.TABLE_B_ID,
   B.TABLE_B_Column
FROM  dbo.TABLE_A   A
INNER JOIN dbo.TABLE_B B
ON   A.TABLE_A_ID        = B.TABLE_A_FK_ID
WHERE EXISTS
 (
 SELECT 1
 FROM dbo.TABLE_C   C 
 WHERE  B.TABLE_B_ID        = C.TABLE_B_FK_1_ID
 AND  C.TABLE_B_FK_1_ID       IS NOT NULL
  
 UNION ALL
 
 SELECT 1
 FROM dbo.TABLE_C   C 
 WHERE   B.TABLE_B_ID        = C.TABLE_B_FK_2_ID
 AND  C.TABLE_B_FK_2_ID       IS NOT NULL
 ) AS C

The result was that the code stormed through this part and we solved the bottleneck.. Thanks Gareth, for dragging this out of your memory and I do note both your comments on this technique

1) "We really  need to test it first for a number of conditions because I am sure the article said it may not work in all cases and we may be making things much worse'. - We did test it for different conditions

2)  Always put the condition that is more likely to success the top part of your UNION ALL statement, because as we are using a EXISTS SEMI-JOIN the database execution will stop as soon as we find a match.. We want this to happen as quickly as possible so make sure that code block where the strongest chance of a match is placed first.

To see a blog entry on how to replace a  UNION ALL with an OR see the link below - I have no idea if this was the article Gareth remembered, but it shows you how to do it..

 http://www.databasechannel.com/AccessArticles/Article_ORAND_UseUnionInsteadofOR.html

 So in this case talking to my Colleague has shown me a new way of doing something.. A collective knowledge base is always greater than a single persons...

BTW I will be returning to this technique shortly

This is a post (well a series of posts) I have been thinking about for some time. Basically, you can add Indexes, Covering Indexes. Materialised Views, filtered Indexes... Partitioning etc.. to your heart’s content... But sometimes it just helps if you know your Business logic....

I once worked on the Data Warehouse system for one of the major uk supermarkets. In this case while the client sold both non-food and food products the food data was dealt with by its own Data Warehouse system. We had a batch process to aggregate data at the Product Store Day level. In other words how much of each Product was sold at each Store on each Day. We also had to work out margin based on a Cost and Selling Prices which changed according to type of Branch (differential pricing).

We also had to deal with the concept of VAT (Value Added Tax) a European Wide form of Purchase Tax. However, in the UK (unlike many countries has the concept of Zero rating most food). I say most most because some foodstuffs have VAT applied (most famously Chocolate Covered Biscuits 'aka the famous Jaffa Cake case), some have Zero Rated VAT (as I say most foods, but surprising Cakes) - and some Foods have VAT applied to some of the Product but not all... The example I was given was that if you but a packet of tea in a box, there is not VAT to pay.. However, if you but the same amount of Tea in a Tin, you have to pay VAT on the cost of the Tin (i.e. a proportion). I have actually now read the VAT Rules from the Inland Revenue as part of my research for this post. Available here:

http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.portal?_nfpb=true&_pageLabel=pageLibrary_ShowContent&id=HMCE_CL_000118&propertyType=document

See Section 6. Mixed supplies: mixtures, promotional linked items and packaging

By the way, having just re-read it I actually think the Tea Tin example does not work - Because the apportion bit has to more than the more than a certain percentage or cost (say £1) to kick in... But it will still work for the business concept I am attempting to describe.

BTW to see why Cakes (and Jaffa Cakes in particular) do not have VAT applied, but Chocolate Covered Biscuits do see here:

http://en.wikipedia.org/wiki/Jaffa_Cakes

Anyway, back to my story...

So to process VAT we have effectively to store 2 pieces of information. A flag (Yes/No) to say if VAT is applicable to the Product and a numeric field to hold the VAT Percentage applicable for those VATable items (100%for Fully VAT, say 10% for our Tea in Tin example or NULL or Zero (Not applicable or Zero).

Note: I accept we could technically hold all this information in 1 numeric field (100 = YES, 0 = NO and a Percentage in between), but this is counter intuitive.

Anyway, each night we had the VAT Process running (via SQL Server Stored Procedures), which took all our sales for the day (we are talking a couple of million records a day here) and calculated the VAT figures... The VAT process was taking a significant portion of our overnight window, and I was asked to look at it to see if I could speed up. This area had been written before I had been on the project, so they probably considered a fresh pair of eyes could help. The first concern I had with the code is that I noticed it was updating the data. Everyone knows that in SQL Server (as with other RDBMS) that an Update statement is costly. As it involves both a physical Delete and an Insert, so immediately you have twice the log file workload. However, while I realised that while I could have probably re-written an update as an Insert into a new table… what the real problem was that it was doing to many updates..  The VAT Flag and Percentage Apportion Field for the ProductStoreDay had been initialised to assume that VAT was applicable (1 and 100%) but we checked our reference product data we then toggled the field to be either (Off – Set *** to be Zero, or set the Apportion field).

But as I said at the top of his article in the UK – MOST Food is Zero rated for VAT. So what the batch process was doing was updating around 1.8 Million records setting a Flag to be 0 (from 1) and a field to be NULL (from 100%) and leaving 200,000 records unchanged.

I realised that if I actually initiated the data to so that the Flag to be 0 and the field to be NULL, and only updating those records which had to have VAT applied, I would cut 80% from the VAT Batch Run… and that is what I did.. It is quicker to update 200,000 records rather than 1.8 million…

I used my Business Knowledge (the fact that I knew most Food is Zero rated for VAT) to change the logic of code and cause a 5 fold increase in performance… and not an Index change in sight….

When I worked out there were at least 6 methods of Inserting data into a Table, I wanted to find out which was the fastest method... I will reveal the surprising fact in another posting. But I thought I would test the performance of each method with a large amount of data.. The first problem I had was how do I create a large number of Insert statements in many different forms? - Well writing Dynamic SQL seemed to be the best option, but to do this I still needed to create (I decided 100000) different rows from which I could build up my SQL Statement.

I decided that I needed to create a TALLY table (sometimes called a Numbers table) with 100000 different values in it.. I knew the fastest method do this was via a CROSS JOIN in a CTE - (I think it was in a Blog post by Jamie Thomson - But I can't find it now) so I googled CTE and Tally and came up with this example here..

 http://beyondrelational.com/blogs/ozrenkrznaric/archive/2011/06/21/tally-table.aspx

IF OBJECT_ID('dbo.Tally') IS NOT NULL

;WITH Ones (N)

AS

(

SELECT 1 AS N

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

UNION ALL SELECT 1

)

,Tens (N)

AS

SELECT 1

FROM Ones A

CROSS JOIN Ones B

)

,Hundreds (N)

AS

SELECT 1

FROM Tens A

CROSS JOIN Tens B

)

,Thousands (N)

SELECT 1

FROM Hundreds A

CROSS JOIN Tens B

)

INSERT INTO dbo.Tally (ID)

SELECT TOP 100000

N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM Thousands;

You can extend this concept to the millions and billions of rows if you so wish.. As an example of the speed it took just over 2 seconds on my laptop to generate the data..

I could then use the TALLY table to create my various different  Insert Statements

e.g.

SELECT 'INSERT INTO dbo.InsertTest1 (ID, TextString) VALUES (' + CAST(ID AS NVARCHAR) + ' , ' + CHAR(39) + 'Value: ' + + RIGHT(CAST((1000000 + ID) AS NVARCHAR (7)), 6) + + CHAR(39) + ');'

FROM dbo.Tally;

SELECT 'INSERT INTO dbo.InsertTest2 (ID, TextString) VALUES'

UNION ALL

SELECT '(' + CAST(ID AS NVARCHAR) + ' , ' + CHAR(39) + 'Value: ' + + RIGHT(CAST((1000000 + ID) AS NVARCHAR (7)), 6) + CHAR(39) + ')' + CASE WHEN ID <> 100000 THEN ',' ELSE '' END

FROM dbo.Tally

UNION ALL

SELECT ';';

SELECT 'INSERT INTO dbo.InsertTest3 (ID, TextString)

UNION ALL

SELECT '(' + CAST(ID AS NVARCHAR) + ' , ' + CHAR(39) + 'Value: ' + + RIGHT(CAST((1000000 + ID) AS NVARCHAR (7)), 6) + CHAR(39) + ')' + CASE WHEN ID <> 100000 THEN ',' ELSE '' END

FROM dbo.Tally

UNION ALL

SELECT ') AS T (ID, TextString);';

 OK Then what is the GOTCHA ?

 Well I on the INSERT Statement Row Constructor I got the following error message

Msg 10738, Level 15, State 1, Line 1021

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

I had not appreciated that there is a LIMIT (of 1000) to the number of lines you can add to a single INSERT Statement when using the row (also known as table) constructor method...

So to insert 100,000 rows I would need 100 batches of 1000 rows using this method....

See also

http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/how-many-different-ways-can-you-write-a-sql-insert-statement-1-2-3-6.aspx

http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-now-you-can-write-an-insert-statement-6-different-ways-why-do-you-need-to-know-this-and-why-is-merge-the-best.aspx

http://consultingblogs.emc.com/paulmcmillan/archive/2011/07/06/so-you-may-have-many-different-ways-of-inserting-data-but-which-is-the-fastest.aspx