-
-
Recently I was investigating why a new server which is in its parallel running phase was taking significantly longer to process the daily data than the server its due to replace.
The server has SQL & SSAS installed so the problem was not likely to be in the network transfer as its using shared memory. As i dug around the SQL dmv’s i noticed in sys.dm_exec_connections that the SSAS connection had a packet size of 8000 bytes instead of the usual 4096 bytes and from there i found that the datasource had been configured with the wrong provider but what was really interesting and the point of the blog is the performance difference which i have shown below.
| |
Rows per second |
| .Net SqlClient Data Provider: |
30,000 |
| SQL Server Native Client 10: |
240,000 |
Thats right! For a single partition, the native client was able to process 240,000 rows per second where as the .net client maxes out at 30,000. That means the SQL Native Client is 800% faster! I knew that the .net providers were slower but I had never gathered any metrics before. If your looking after a SSAS server I would definitely recommend taking a few minutes to check which provider is configured in the datasource.
Another point to consider is you may have a custom solution that is doing your ETL and utilising the .net providers. This would also be impacted by the .net provider throughput limits and a switch over to SSIS could dramatically improve your ETL.
-
-
Over the years i have come across a few situations where server side connections to SQL server fail when you use a DNS alias that points back to the server your initiating the connection from but you can connect remotely.
Its an annoying problem which has a very unhelpful error message that changed in different versions of SQL. In SQL 2000 you are presented with "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." and in SQL 2005 + SQL 2008 its “Login failed. The login is from an untrusted domain and cannot be used with Windows authentication”. You will also see eventid 537 in the security logs
One of the most common reasons a system is setup with an alias pointing back on itself is because a consolidation has taken place and you don’t want to change the connection strings. However some people simply got burnt when Microsoft first released the security patch which introduced this change and i still find people being burnt today.
Cause
NTLM reflection protection was introduced as part of security fix MS08-068. This causes a local authentication failure when using a dns alias which bubbles up and becomes the error descrived above.
Relevant MS Articles are MS08-068 & http://support.microsoft.com/kb/926642 and cause extract is:
This problem occurs because of the way that NT LAN Manager (NTLM) treats different naming conventions as remote entities instead of as local entities. A local authentication failure might occur when the client calculates and caches the correct response to the NTLM challenge that is sent by the server in local "lsass" memory before the response is sent back to the server. When the server code for NTLM finds the received response in the local "lsass" cache, the code does not honor the authentication request and treats it as a replay attack. This behavior leads to a local authentication failure.
Solution
You either need to use the local name rather than DNS alias or there are steps described in the resolutions section of the articles to disable the protection totally or for a specific alias.