When you read the title, I'm pretty sure that you asked "Why do I need to do this web service consuming things in a Script Task although I can use ready-to-use Web Service Task ?".
Well... I faced issues for some scenarios that you will need to consume the web service in a Script Task as a workaround. For a customer scenario, we had issues with this scenario (WebService Task + HTTP Connection Manager + Proxy Server with NTLM Authentication) and we fixed their issue doing all the things in a Script Task.
As an SSIS Developer, "Script Task" is there for nearly all your needs as .NET is there waiting for you. If you can't do something with the built-in SSIS tasks, try to do the same functionality inside a Script Task.
As "Script Task" is a "Task" already, you can take the input from SSIS Variables or the Input Columns you attached to the input of the Script Task. You can send data out of Script Task again to SSIS Variables or the Output Column you will define.
Let's go back to the web service consuming scenario ...
Here's the Script Task code that I used to consume a web service in a Script Task :
Public Sub Main()
Dim strProxyURL As String = "http://www.yourproxyurl.com:NNNN"
Dim strProxyUsername As String = "myusername"
Dim strProxyPassword As String = "mypassword"
Dim strProxyDomain As String = "MYDOMAIN"
Dim myProxy As WebProxy = New WebProxy(strProxyURL, True) ' //bypass on local = true
myProxy.Credentials = New NetworkCredential(strProxyUsername, strProxyPassword, strProxyDomain)
'instantiate a web service object
Dim svc As New myWebService()
'set our proxy object to the webservice object's proxy property
svc.Proxy = myProxy
'create a DataSet to have the result from the method
Dim ds As DataSet = svc.WeatherInfo("Istanbul", "Turkey")
'The 1st DataTable has the output. Write this XML content to a text file
ds.Tables(0).WriteXml("C:\\WebServiceResults.xml")
Dts.TaskResult = Dts.Results.Success
End Sub
Here in this script task, I'm not taking anything from the "flow" or SSIS Variables. It works and writes the result to an XML file.
I want to underline an important point about namespaces. By default, the references below are added to a Script Task :
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
But we will need to add the references below also :
Imports System.Net
Imports System.IO
Imports System.Text
Imports System.ComponentModel
Imports System.Diagnostics
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml.Serialization
Imports System.Xml
Let's go over the script. First of all, I'm defining a couple of string variables to build up a WebProxy class to use it with the web service proxy class. Then the tricky line comes in :
'instantiate a web service object
Dim svc As New myWebService()
This is the tricky part of this implementation. As you can see I'm instantiating a "myWebService()" object. This "myWebServcice" is a class that I defined below in the Script Task code. But I did not wrote this class by hand. I used our "wsdl.exe" tool which is coming with .NET Framework. This tool generated code for web service clients from WSDL file. You can refer to http://msdn.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx for details.
I used the line below to generate the VB.Net code :
Wsdl.exe /l:VB /n:myWebService /out:myWebService.vb http://www.mywetherinfo.com/myWebService.asmx?WSDL
This command created the myWebService.vb file with VB.Net code for the web service defined in the WSDL URL http://www.mywetherinfo.com/myWebService.asmx?WSDL
Then I added the contents of the myWebService.vb file into my script task. That's it :)
As I said, this might not be useful for all scenarios. I think that I gave an example for the power of Script Task at least :)
P.S. : Luckily the "Add Web Reference" feature is available in SSIS 2008 Script Task. So you won't need to do all those things in SSIS 2008. As you do in the way you used to do in your WinForms, WebForms apps in your other Visual Studio 2008 Projects; just add your web reference and use it in your Script Task ;)
One of our customers was saying that their SSIS Project with some complex "Data Flow Task"s with lots of Lookup Transformations was opening very slowly in BIDS ("Business Intelligence Development Studio") or I don't know if I should say "Visual Studio" ...
The very first thing that I check was "validation" part. As you know, by default all tasks are set to validate its incoming and/or outcoming parts to protect SSIS package developers for possible metadata changes in source/destination data sources. For example, another developer may change a column's datatype in a table without letting you know for an OLE DB Source connecting to a SQL Server, Oracle etc. If the component sees a validation problem it will warn you with an exclamation icon on itself and if you hover on the component, you'll see the detail as a tooltip.
But sometimes this "validation" part might be really time consuming for really complex tasks that you developed. If that is the case, you should be aware of some properties like ValidateExternalMetadata and DelayValidation .
We do have a very nice document here explaining what are they for :
http://msdn.microsoft.com/en-us/library/ms137625(SQL.90).aspx
Enough reference info :) Let's go back to our story ...
Since our customer's Data Flow Task was containing lots of transformations I asked customer to change "DelayValidation" properties for the tasks as "True" and "ValidateExternalMetadata" to "False. It didn't help again ...
The same project was working fine on my same testing environment. After digging around, we noticed that customer installed "BIDS Helper" and it was doing some extra jobs. Noticed the text in the status bar of BIDS was saying "BIDS Helper: Highlighting Expressions and Configurations" . Without "BIDS Helper", a sample complex Data Flow Task was opening in a few seconds but with BIDS Helper, it was taking 3 or 4 minutes.
Asked customer to remove "BIDS Helper" and see that the complex data flow task is opening in seconds on customer's machine too.
I'm not very familiar with "BIDS Helper" but the thing that I know is BIDS Helper is the winner of SQL Heroes 2008 Contest .
If you've installed any Visual Studio add-in and experienced a slow performance in design mode with BIDS while opening the tasks, try disabling/removing the add-ins and test without all those add-ins.
Note: After publishing this blog post, BIDS Helper developers Greg Galloway, John Welch and Darren Gosbell reached me and wanted me to add their comment below :
The BIDS Helper team has spent hundreds of hours working on making the expression highlighter be as efficient as possible. (Unfortunately, the SSIS APIs to determine what's controlled by an expression are rather sluggish.) That being said, we would really like to see this feature included in the SSIS product itself, since it will be able to highlight expressions and configurations more efficiently than BiDS Helper. I would urge everyone to vote for this request on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=170582
But until this gets included in the product, I would suggest a few things:
1. Check you've got the lastest BIDS Helper installed. Significant performance improvements in the expression and configuration highlighter have been made since the first release.
2. Consider disabling the expression highlighter using the Tools... Options... BIDS Helper settings screen. And consider using the BIDS Helper Expression List to see what's controlled by expressions instead:
http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Expression%20List&referringTitle=Home
We feel the BIDS Helper Expression Highlighter is a brilliant feature that's helpful 95% of the time. If you have a large, complex data flow where it is slow, you're welcome to disable the expression highlighter.
If you have suggestions on improving this feature, feel free to post them to the Issue Tracker tab:
http://www.codeplex.com/bidshelper/WorkItem/List.aspx
One of our customers had reported that when they tried installing a decent Microsoft XML related patch from a Microsoft Security Bulletin, it was not allowing them to use the usual command line switches like "/norestart" "/quiet" etc. on some of their machines.
As you know "Windows Installer" is a windows service running(1) on all Windows machines which is helping the packages packed with Windows Installer. Microsoft patches/hotfixes are also packaged using Windows Installer.
Sometimes, system administrators may need to use command line switches while deploying those patches to lots of machines and they might need to use those command line switches in their network scripts(2) .
You can find the details in MSDN http://msdn.microsoft.com/en-us/library/aa372024(VS.85).aspx for all the command line switches available.
I asked our customer to run the VBScript below to have their Windows Installer version :
Set WshShell = WScript.CreateObject("WScript.Shell")
Dim objInstaller
Dim strVersion
Set objInstaller = CreateObject("WindowsInstaller.Installer")
strVersion = objInstaller.Version
MsgBox("Installer version is: " & strVersion)
and we got 2.00.3790.0 and it was a very old version of the Windows Installer. Windows Installer "2.0" version had released with Windox SP RTM/SP1 , Win2K SP3, Win2K3 RTM .
Asked customer to apply the latest service pack for the failing machines and see that switches started working again on the failing machines without issues.
Long story short, please be sure that your Windows is up-to-date. Windows Update and/or Microsoft Download Center is there and waiting for you ;-)
(1) : Maybe saying the services stays there in not started mode but it switches "Started" state when needed.
(2) : Needless to say, some system admins are already using "Microsoft System Center Configuration Manager" , and don't experience such issues :-)
It has been reported by one of our customers that they have developed an SSIS package which needs to connect to an FTP Server on their internal network to download some files and take some data from the files to put it into some destination like a SQL Server table after doing some conversions/calculations etc.
But the FTP Server connection was failing when they tried connecting to FTP Server within SQL Server Business Intelligence Studio (BIDS). On the other hand, when they tried connecting to the very same FTP Server using "ftp" command from a command line it was working without issues. They also can connect to the FTP Server from Internet Explorer. It was looking quite strange to me ...
After doing lots of tests, we've found out that the antivirus software installed on the machine was blocking the FTP traffic! We removed this FTP traffic blockage within the antivirus software and package started to work without issues.
Don't you also think like me that the antivirus softwares are trying do a lot more than we expect them to do ? I can hear your voice saying "But the virus writers are adding new techniques to the viruses day by day and some of them can try to reach an FTP servers on the net ? ". Ok that's true. But the viruses also can do the lots of similar things over HTTP. Ain't it ? They are already doing such things a lot. Will we block HTTP traffic too ? ;)
Let's say you have got a SQL Server 2005/2008 cluster with two nodes. Here's the configuration in detail :
Cluster name : MYCLUSTER
Cluster IP : 192.168.0.10
Node 1 (active node) hostname : NODE1
Node 1 IP : 192.168.0.11
Node 2 (passive node) hostname : NODE2
Node 2 IP : 192.168.0.12
The SQL Server Instance name : INST1 which is listening on TCP 1433 on both nodes (Of course the one on NODE2 is not working now)
Let's say your client application wants to connect to this SQL Server 2005/2008 cluster with "MYCLUSTER\INST1" syntax and there's a firewall in front of this SQL Server 2005/2008 cluster and you already opened the traffice for all 3 IPs (192.168.0.10, 192.168.0.11, 192.168.0.12) for TCP 1433.
But you're still receiving "SQL Server does not exist or access is denied" error message.
Since you're using "SERVERNAME\INSTANCENAME" syntax to connect to a SQL Server 2005/2008 instance your client needs to talk to SQL Server 2005/2008 Browser Service (http://msdn.microsoft.com/en-us/library/ms181087.aspx) . As soon as the SQL Server connectivity related libraries installed on your machine sees "\" character in the SQL Server name that you gave to him, it looks for a SQL Server Browser Service on "SERVERNAME" machine.
Question : Why SQL Server Browser Service is needed ?
Answer : Because the client does not have a clue which TCP port the "INSTANCENAME" instance is listening on. In our scenario the INSTANCENAME is listening on TCP 1433 of course but you may have lots of SQL Server instances on your cluster and if this is the scenario, the TCP port will be defined by this SQL Server instance randomly.
Question : Can I send a query manually to the SQL Server Browser service ?
Answer : Sure but since it's not listening on TCP but UDP, you'll need to use another tool than telnet. We do have a tool called "PortQryUI" which you can download it from http://www.microsoft.com/downloads/details.aspx?familyid=8355e537-1ea6-4569-aabb-f248f4bd91d0&displaylang=en . Running "PortqueryUI.exe" will show up a GUI and if you enter the servername or IP into the textbox at the top and select "Manually input query ports:" radio button. Type 1434 into "Ports to query : " textbox and select UDP from "Protocol" combobox and click on "Query" button. Look for "Server's response" in Query Result. Here's an example output from my testing machines :
As you can see, the SQL Server Browser Service returns lots of info. Especially the protocols of the instances which listens. "NP" means "Named Pipes" and TCP means TCP/IP of course :)
SQL Server Browser Service listens on UDP 1434 and you cannot modify this port. SQL Server Browser Service is a "Windows Service" and the first thing that you should check is if the service is running. Typing "services.msc" from Start/Run will take you to the windows services list. Also you can type "netstat -ano" from a command prompt and look for a process which is listening on UDP 1434 like below :
(P.S. : UDP protocol list is coming after TCP Protocol lines)
As you can see from the above screenshot, a process with 2356 PID is listening on 0.0.0.0:1434 means it listens on ALL network interfaces on the machine. In our SQL Server Cluster case, while the Node1 is the active node, SQL Server Browser will listen on both 192.168.0.10 (Cluster IP) and 192.168.0.11 (Node 1 IP). If the cluster fails over to the other node, the SQL Server Browser Service running on the Node 2 which is already listening on 192.168.0.10 (Cluster IP) and 192.168.0.11 will start responding to UDP 1434 requests.
Now it's time to refer to the tricky note about SQL Server Browser Service in SQL Server Books Online http://msdn.microsoft.com/en-us/library/ms181087.aspx :
Clustering
SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.
|
Note: |
|
When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters. |
What does this "not a clustered resource" mean ? If a resource is clustered in a Windows Cluster, in a failove the resource will be transferred from the current node (or we can say "failing node") to the other node.
SQL Server Service itself , the main SQL Server Engine is a clustered resource and when a failover occured, the resource show up on the other node as soon as failover operation completed. This means, the service that you're expecting to connect which should be listening on TCP 1433 (or the TCP port that you set) will be always available.
Howewer, this is not true for SQL Server Browser Service since it's not clustered. The SQL Server Browser Service should always be runnin on all your nodes in case of a failover.
Also , as I said before, the SQL Server Browser Service is listening on ALL network interfaces. If your query reach from the cluster IP or the node IP, SQL Server responds to this request. But there's another tricky part : SQL Server Browser service respond from the Node IP. The picture will be like this :
Client (client source port: random, destination port : UDP 1434) <==> Firewall <==> SQL Server Browser Service (listens on UDP 1434 and respond from Node IP/UDP 1434)
Let's analyze this flow from the beginning.
1) Client (let's say its IP address is 192.168.5.5) sends a request to SQL Server Cluster IP address (192.168.0.10) since the user put MYCLUSTER\INST1 syntax (Remember when we used this syntax, a query needs to be send to MYCLUSTER host which is our cluster's virtual network name). Of course a random client source port is defined on client machine for this request. Let's say it's 5449.
2) This request reaches to the firewall and since we have got a allow UDP 1434 rule in firewall, it permits this packet and pass it to the SQL Server Cluster IP
3) Since the current active node is Node1, the SQL Server Browser running on Node 1 takes the request from 192.168.0.10 (Cluster IP) and prepares the result and sends it from 192.168.0.11 (Node IP) to the Client IP (192.168.5.5) and destination port as 5449.
4) Now your firewall should permit this return request from the SQL Server Browser service Node IP (192.168.0.11) to the client (192.168.5.5, dst port : 5449). Don't forget that on the return way the port is not UDP 1434, it's a random port 5449.
5) The client receives the request from port 5449 and the resolution completes. At this point SQL Server Browser Service completes its job and SSRP (SQL Server Resolution Protocol) ends.
6) From this point, the database network library on the client machine starts trying to connect to the TCP port (in our example TCP 1433) it gathered with SSRP and of course this TCP 1433 port should be open in the firewall.
If you want to be sure that something is not blocked by the firewall, taking network captures with Microsoft Network Monitor from both sides (client machine and the SQL Server machine) and analyzing the captures for UDP 1434 traffice will clarify the questions before fighting with your firewall admins ;)
P.S. : SQL Server 2000 is also using SSRP protocol. But it's not a separate service as in SQL Server 2005/2008. Thereofre don't look for a "SQL Server Browser Service" service in Control Panel/Services on a SQL Server 2000 installation. When you started the SQL Server instance, the SQL Browser Service starts within the SQL Server Service.
Microsoft JET (Joint Engine Techonology) Database Engine is an implementation of JET Red and it should not be mixed with the JET Blue ESE (Extensible Storage Engine) which is the core of Microsoft Exchange and Active Directory.
We had released the first versions of the JET OLE DB Provider and JET ODBC Drivers in 90s and it had become the part of MDAC (Microsoft Data Access Components).
JET was helping Database developers to reach Access, Excel, CSV file based data sources. Think about the old non-managed VB times. There was no web, no intranets etc. So the apps were mostly running on top of one machine and the locking, concurrency, ACID were not the things that you should consider while developing your app. Because there was just one user out there playing with those datasources through your app.
When the NT4 Option Pack released, classic ASP became very popular in web programming world and the "old database programmers but new web programmers" still wanted to stay in their old "database-like" things and thought that they can go with these old products on the web. But those "database-like " things were staying behind of the RDBMS (Relational Database Management Systems) products like SQL Server 6.5, 7.0 because of its designs.
When we're talking about web applications, we're starting to talk about "multi-threaded" applications because of their nature. You're putting your code on a server and every dynamic web page request which is feeding the page from a database was doing something with this data source/database.
The some of the web developers noticed the underlying problems of this structure and started moving their database backend to SQL Server or another RDBMS. Because it was handling lockings, queuing, caching etc. all the things in its engine but the JET family data sources were not ready for this new web age.
.NET appeared into the scene and ADO.Net supports OLE DB and ODBC through System.Data.OLEDB* , System.Data.ODBC* namespaces. But if your OLE DB Provider or ODBC Driver is not thread-safe like JET you're still in trouble.
You still can use JET OLEDB Provider or JET ODBC Driver in your ASP.NET code but you're alone because our KB article titled "Using Microsoft Jet with IIS" in http://support.microsoft.com/kb/299973 says that :
When you need unlimited users, 24x7 support, and ACID transactions, Microsoft strongly recommends that you use Microsoft SQL Server with Internet Information Server (IIS). Although Active Server Pages (ASP) works with any OLE DB and ODBC-compliant database, IIS has been extensively tested and is designed to work with Microsoft SQL Server with high transaction traffic and unlimited users that can occur in an Internet scenario.
The similar explanations and details can also be found in a similar KB article titled "ACC97: Using Microsoft Jet with IIS" in http://support.microsoft.com/kb/222135 .
Also, if your machine is a Windows 2003 on a x64 CPU or Windows 2008 on a x64 you'll notice that there's no JET available for your OS as 64bit. This means that if your application is compiled as 64bit and you're trying to run on your Windows 2003 x64 OS, it will faill. If it's the case and if it's possible, you'll need to compile the app as 32bit and go with 32bit version of JET because it's still available as 32bit only on Win2003 or Windows 2008 x64 OS. (You may refer to http://blogs.msdn.com/farukcelik/archive/2007/12/31/udl-test-on-a-64-bit-machine.aspx for the details if you're developing with OLE DB).
Happy coding :)
As we all database developers know that a ODBC DSN is an entry that we created through "ODBC Data Source Administrator" that we reached from Start/Control Panel/AdministrativeTools or typing "odbcad32" from Start/Run .
On a 64bit machine when you run "ODBC Data Source Administrator" and created an ODBC DSN, actually you are creating an ODBC DSN which can be reachable by 64 bit applications only.
But what if you need to run your 32bit application on a 64 bit machine ? The answer is simple, you'll need to run the 32bit version of "odbcad32.exe" by running "c:\Windows\SysWOW64\odbcad32.exe" from Start/Run menu and create your ODBC DSN with this tool.
The key point here to remember is a 64bit machine should be considered as a "64 bit Windows + 32 bit Windows" at the sametime. That's why the folder that our developers put the 32bit "odbcad32.exe" under C:\Windows\SysWOW64\ . The WOW abbreviation here means "Windows on Windows" ;)
That's why, it might be a good idea to have a look at the C:\Windows\SysWOW64\ for the EXE files at least to know what sort of other EXEs/DLLs are also compiled as 32bit and shipped within your 64bit OS.
For example cscript.exe and wscript.exe are ther for your WSH (Windows Scripting Host) scripts which needs to be run as 32bit. Think about a scenario like that. Only the 32 bit version of the OLE DB Provider or ODBC Driver exists for the data source that you need to get the data from your VBScript code. If it's the case, you'll need create the ODBC DSN (assuming that you're going to use ADO + ODBC in your VBScript code) from C:\Windows\SysWOW64\odbcad32.exe and also you should call your script as
"C:\Windows\SysWOW64\wscript.exe C:\scripts\myscript.vbs".
Doubleclicking the myscript.vbs from the Windows Explorer will result your script to be executed as
"C:\Windows\System32\wscript.exe C:\scripts\myscript.vbs".
Hope I managed explaining the WOW philosophy for a 64 bit Windows a little bit in my words and can help you in your projects :)
One of our customers has been reporting that they cannot see their "System Data Sources" under "System DSN" tab when they clicked on "ODBC Data Source Administrator" Control Panel icon. They were also trying to add a new DSN, the wizard was completing succesfully but the DSN was not appearing in the list. Strange.. Ain't it ?
To understand what is going on in this scenario, we need to know the things going under the cover when we're adding a System DSN using "ODBC Data Source Administrator" Control Panel icon (By the way, I prefer typing "odbcad32" in Start/Run rather than going to Control Panel to reach "ODBC Data Source Administrator" tool)
An ODBC System DSN is othing more than some registry entries under HKLM\Software\ODBC (and/or HKCU\Software\ODBC).
The HKLM\Software\ODBC contains two main keys :
- 1) HKLM\Software\ODBC\ODBC.INI
- 2) HKLM\Software\ODBC\ODBCINST.INI
1) HKLM\Software\ODBC\ODBC.INI : Contains the values for your specific driver under your DSN name. For example, if you've created a DSN named "MyDSN" against a SQL Server 2005 with SQL Native Client (SNAC) ODBC Driver, you're going to see a HKLM\Software\ODBC\ODBC.INI\MyDSN key on the left pane of the registry editor and some SNAC related parameters and the values you've entered during DSN setup. For example pairs like Database=pubs, Driver=C:\Windows\system32\sqlncli.dll, LastUser=benjaminlinus, Server=DHARMASERVER1, Trusted_Connection=Yes. (Yes, I'm a huge L.O.S.T fan ;) )
Under this HKLM\Software\ODBC\ODBC.INI key there's an important key named HKLM\Software\ODBC\ODBC.INI\ODBC Data Sources . This key contains the name of the DSN and the type of the ODBC Driver that you've created.
2) HKLM\Software\ODBC\ODBCINST.INI : Contains all the "ODBC Drivers" existing on your machine (and just a few additional ODBC Core related keys)
Putting al those information in mind, I dived into the registry of the problematic server and walked through those registry keys and all were looking fine. Also the newly created DSNs were coming there under the registry places as they should be.
My colleague Olga (big thanks goes to her) warned me about checking the "(value not set)" values for the keys. Following her directions, I decided to take a registry export as .REG file of HKLM\Software\ODBC from the problematic Windows 2003 SP1 machine. Also got an export from my test box, compared and the thing that I noticed was surprising.
I noticed that value for "(Default)" is set to "(value not set)" under HKLM\Software\ODBC , HKLM\Software\ODBC.INI and "HKLM\Software\ODBC.INI\ODBC Data Sources" keys. But differently the value "(value not set)" has been entered manually. I mean in somehow this "(value not set)" has been entered as a value.
Normally Windows Registry Editor shows the value "(value not set)" for something if its value really not set. But of course, you can enter this "(value not set)" string by manually and the value looks same with the "real" "(value not set)".
When you've double clicked on "(Default)" you'll get "Edit String" dialog box like below if the value is really "(value not set)"; you should see nothing in "Value data" textbox :
But it was like below in the problematic keys; the "(value not set)" string value has been added there manually like that :
We've gone through all the keys under HKLM\Software\ODBC until being sure that there are no "problematic" values left. After this "cleaning up" process and all DSNs started showing up. We've also created new DSNs and see that they're showing up in DSNs list.
Sometimes pools that we should pay attention to their "hygiene" are not only swimming pools. We should also be careful about our database "connection pool"s.
Here comes the story behind this "philosophical" entrance ...
One of our customers was reporting an issue when they were about to put their web server farm on production. Strangely they were receiving an error message about the exhaustion of connections in the connection "pools" even though there was no traffic coming out of the internet yet. The error messages were coming into EventLog with a call stack like that :
"An unhanled exception has occurred"
Exception information:
Exception type: InvalidOperationException
Exception message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Request information:
Request URL: http://private.somedomain.com/somefolder/Default.aspx
Request path: /somefolder/Default.aspx
User host address: 192.168.1.11
User:
Is authenticated: False
Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 666
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
By looking at the very bottom line "System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)" of the call stack, we can clearly see that the web application is storing the ASP.NET Session State information into a SQL Server. Storing the ASP.Net Sessions into a SQL Server and sharing the session info on all the web servers in a web farm is a very common scenario.
After talking to our customer, I learned that they've some number of hardware "load balancers" in front of the web servers. Customer had already checked the IIS logs and noted that all the web requests were "heartbeat HTTP requests" coming out of the load balancers.
They had defined this heartbeat URL as http://private.somedomain.com/somefolder/Default.aspx in the load balancers.
If you carefully look at the URL, the load balancers had been pointed to an .ASPX page. Since it's an APS.Net page, the web servers were trying to create a ASP.Net Session and store the session info in SQL Server for those "heartbeat" requests.
Since the load balancer's HTTP client implementation (think about like there's a web browser in the load balancer firmware) was not accepting HTTP Cookies , all the requests coming from the load balancers had been thought a "new session" by the web servers.
Additionally, load balancers had been configured to check the web servers in 2 second periods. Of course as the number of such web requests arise in the time, the connection pool was getting "full".
IMHO, it's not the fault of the load balancer HTTP client implementation. I don't think that a load balancer HTTP client implementation has to implement all of the HTTP protocol . They might not accept cookies, they don't have to be as powerful as Microsoft Internet Explorer. HTTP code in the load balancer firmware is only there for just a simple "heartbeat" HTTP check. All other additional features would be an overhead for a load balancer.
Long story short, we needed to find a way for web servers to not to create an ASP.NET Session for the requests coming out of the load balancers.
By relying on that idea, we've put a static HTML file (loadbalancertest.html) to somewhere in the web servers and configured the load balancers to check the URL pointing to this new static HTML file : http://private.somedomain.com/somefolder/loadbalancertest.html and this resolved the issue.
Now the connection pool created for ASP.Net Session Storing mechanism is dealing with only the correct connections coming from correct web requests.
Happy swimming :)
If you had developed a VS 2005 project containing ADOX references on a Win XP machine and moved the project to a Vista machine, VS 2005 may not access to ADOX reference.
Then you'll need to use the "tlbimp.exe" (Type Library Importer) tool which is shipped with Visual Studio 2005 (full path is "\Microsoft Visual Studio 8\SDK\v2.0\Bin\TlbImp.exe") . Tlbimp.exe can convert the type definitions within a COM type library into equivalent definitions in a CLR assembly.
Here are the steps to get your VS2005 work on Vista :
1) Copy the "%Program Files%\Common Files\System\ado\msadox.dll" from working XP machine to Vista machine
2) Execute "tlbimp msadox.dll /output:MSADOXASM.dll" . This will create the CLR assembly file "MSADOXASM.dll"
3) Add this assembly to your project and add the "using MSADOXASM;" statement to your code.
4) The references will start working without issues
Let's say you've got a SQL Server (KIRK) containing a linked server against another SQL Server (SCOTTY) and you're connecting the KIRK from remote client machine (SPOCK) using SQL Server Management Studio and receiving the error message below when you executed a query involving the linked server :
"Error 18456: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'" error message.
This error message indicates that the required credentials are not "forwarded" to the end SQL Server SCOTTY . We call this issues as "Double hop" issues and the only way to get this to work is using Kerberos Authentication in the scenario.
We already have a KB article 319723 titled "How to use Kerberos Authentication in SQL Server" and explains the problem with an example which is having IIS in the middle. The picture is like that for the example in the KB article :
Client Machine (running Internet Explorer) --> IIS Machine (hosting ASP Pages) --> SQL Server
Though the idea is same, it's a little bit different for our scenario. First of all, i need to give some more details about my environment :
- FARUKCDC is my Active Directory machine and my domain is farukcorp.com
- SPOCK.farukcorp.com is my client machine and it has got only SQL Server 2005 Client Tools to be able to use SQL Server Management Studio
The current logged on user to the machine is FARUKCORP\Spockuser
- KIRK.farukcorp.com is my SQL Server in the middle and it has SQL Server 2005 with a default instance listening on TCP 1433. The service account running the SQL Server Service is FARUKCORP\Kirkuser
The current logged on user to the machine is FARUKCORP\Kirkuser again.
FARUKCORP\kirkuser is also added to the local Administrators group on KIRK machine
- SCOTTY.farukcorp.com is my end SQL Server and it has SQL Server 2005 with a default instance listening on TCP 1433 again. The service account running the SQL Server is FARUKCORP\scottyuser
The current logged on user to the machine is FARUKCORP\scottyuser again
FARUKCORP\scottyuser is also added to the local Administrators group on SCOTT machine
Here's the steps to reproduce the issue :
- 1) The FARUKCORP\Spockuser who is logged on to the machine runs Management Studio
- 2) Connects to SQL Server named KIRK
- 3) Tries to retrieve data from a linked server named "linkedserver" in KIRK
- 4) The "linked server" is linked to the SQL Server named SCOTTY
- 5) User executes the query below
select * from linkedserver.master.dbo.sysdatabases
and receives the error message :
"Error 18456: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'"
Here are the steps that you'll need to check/apply to get this Kerberos delegation scenario to work :
1.) Testing the connectivity without Kerberos delegation :
Because we want the user logged on to the SPOCK machine to access the database in SCOTTY, we need to be sure that we can connect from
SPOCK à KIRK and SPOCK à SCOTTY. After being sure of that, we'll set the delegation setting later
1.1.) SPOCKàKIRK connectivity test :
I granted access to FARUKCORP\Spockuser in KIRK SQL Server. After that, I opened Management Studio on SPOCK machine and see that I can connect to KIRK machine successfully.
1.2.) SPOCKàSCOTTY connectivity test :
I granted access to FARUKCORP\Spockuser in SCOTTY SQL Server. After that, I opened Management Studio on SPOCK machine and see that I can connect to SCOTTY machine successfully.
2.) Setting the Kerberos Delegation for the computers (KIRK , SCOTTY):
I logged onto the FARUKCDC domain controller machine logged in with domain admin account and opened "Active Directory Users and Computers". I followed the following steps to enable "Trust" for the computers :
- a. Under my domain farukcorp.com, I expanded Computers and right mouse click onto SCOTTY machine and selected Properties
- b. Switched to "Delegation" tab and selected "Trust this computer for delegation to any service (Kerberos only)"
- c. Followed the step b for the SCOTTY machine too :
3.) Setting the SPNs for the computers (KIRK, SCOTTY) :
If the SQL Server Services was running with LOCAL SYSTEM or NETWORK SERVICE account, the SQL Server Services would create the required for its services automatically. Because we're running the SQL Servers services with our specified domain account (like FARUKCORP\Kirkuser and FARUKCORP\scottyuser) we need the set the SPNs manually. I followed the steps below to add the SPNs for the computers :
To download the SetSPN utility, visit the following Microsoft Web site: http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&displaylang=en
- a. Opened a command shell by typing "cmd" from the Start/Run menu
- b. I issued the command below and got the results for the HOST SPNs for KIRK :
C:\Documents and Settings\Administrator>setspn -l kirk
Registered ServicePrincipalNames for CN=KIRK,CN=Computers,DC=farukcorp,DC=com:
HOST/KIRK
HOST/KIRK.farukcorp.com
- c. I issued the command below and got the results for the HOST SPNs for SCOTTY:
C:\Documents and Settings\Administrator>setspn -l scotty
Registered ServicePrincipalNames for CN=SCOTTY,CN=Computers,DC=farukcorp,DC=com:
HOST/SCOTTY
HOST/SCOTTY.farukcorp.com
- d. I issued the command below and got the results for the HOST SPNs :
C:\Documents and Settings\Administrator>setspn -l spock
Registered ServicePrincipalNames for CN=SPOCK,CN=Computers,DC=farukcorp,DC=com:
HOST/SPOCK
HOST/SPOCK.farukcorp.com
- e. I got that the HOST SPNs are set but the MSSQLSvc SPNs are NOT set for the KIRK and SCOTTY computers
- f. I issued the commands below to add the MSSQLSvc SPN for KIRK machine :
- 1) To add:
C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/KIRK.farukcorp.com farukcorp\kirkuser
Registering ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com
MSSQLSvc/KIRK.farukcorp.com
Updated object
C:\Documents and Settings\Administrator>setspn -l farukcorp\kirkuser
Registered ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com:
MSSQLSvc/KIRK.farukcorp.com
C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/KIRK.farukcorp.com:1433 farukcorp\kirkuser
Registering ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com
MSSQLSvc/KIRK.farukcorp.com:1433
Updated object
C:\Documents and Settings\Administrator>setspn -l farukcorp\kirkuser
Registered ServicePrincipalNames for CN=Kirkuser,CN=Users,DC=farukcorp,DC=com:
MSSQLSvc/KIRK.farukcorp.com:1433
MSSQLSvc/KIRK.farukcorp.com
You need to see both of these yellow colored lines for farukcorp\kirkuser user.
- g. I issued the same commands in step f, like below to add the MSSQLSvc SPN for SCOTTY machine :
C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/SCOTTY.farukcorp.com:1433 farukcorp\scottyuser
Registering ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com
MSSQLSvc/SCOTTY.farukcorp.com:1433
Updated object
C:\Documents and Settings\Administrator>setspn -l farukcorp\scottyuser
Registered ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com:
MSSQLSvc/SCOTTY.farukcorp.com:1433
C:\Documents and Settings\Administrator>setspn -A MSSQLSvc/SCOTTY.farukcorp.com farukcorp\scottyuser
Registering ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com
MSSQLSvc/SCOTTY.farukcorp.com
Updated object
C:\Documents and Settings\Administrator>setspn -l farukcorp\scottyuser
Registered ServicePrincipalNames for CN=scottyuser,CN=Users,DC=farukcorp,DC=com:
MSSQLSvc/SCOTTY.farukcorp.com:1433
MSSQLSvc/SCOTTY.farukcorp.com
You need to see both of these yellow colored lines for farukcorp\scottyuser user.
4.) Setting the permissions to read/write servicePrincipalNames for the FARUKCORP\Kirkuser, FARUKCORP\scottyuser domain accounts :
I followed the steps below In my FARUKCDC Domain Controller machine :
|
1. |
Click Start, click Run, type Adsiedit.msc, and then click OK. |
|
2. |
In the ADSI Edit snap-in, expand Domain [farukcorp.com], expand DC= farukcorp.com, expand CN=Users, right-click CN= Kirkuser, and then click Properties. |
|
3. |
In the CN= Kirkuser Properties dialog box, click the Security tab. |
|
4. |
On the Security tab, click Advanced. |
|
5. |
In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.
If SELF is not listed, click Add, and then add SELF. |
|
6. |
Under Permission entries, click SELF, and then click Edit. |
|
7. |
In the Permission Entry dialog box, click the Properties tab. |
|
8. |
On the Properties tab, click This object only in the Apply onto list, and then make sure that the check boxes for the following permissions are selected under Permissions:
|
|
Read servicePrincipalName |
|
|
Write servicePrincipalName | |
|
9. |
Click OK three times, and then exit the ADSI Edit snap-in. |
10. Repeat the same 9 steps above for scottyuser also.
5.) Setting the Kerberos Delegation for the domain users running the SQL Server accounts (FARUKCORP\Kirkuser and FARUKCORP\scottyuser) :
Again FARUKCDC domain controller machine, logged in with domain admin account and opened "Active Directory Users and Computers". I followed the following steps to enable "Trust this user for delegation to any service (Kerberos only)" for the users this time:
- a. Under my domain farukcorp.com, I expanded Users and right mouse click onto Kirkuse user and selected Properties
- b. Switched to "Delegation" tab and selected "Trust this user for delegation to any service (Kerberos only)""
- c. Followed the step b for the scottyuser user too :
6.) Setting up the linked server
I switched to the KIRK machine, logged on to the KIRK SQL Server Engine with Windows Authentication (using FARUKCORP\Kirkuser account) and opened a SQL Query Window, typed the SQL statements below to set up the linked server :
- a. Drop the linked server if exists :
EXEC sp_dropserver 'LinkedServer'
- b. Create the linked server named "LinkedServer" :
- c.
EXEC sp_addlinkedserver @server='LinkedServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='scotty.farukcorp.com',
@provstr='Integrated Security=SSPI;'
EXEC sp_addlinkedsrvlogin 'LinkedServer', true
- e. Check if the "LinkedServer" exists in sys.servers :
select * from sys.servers
- f. Check if the "LinkedServer" works from here (You should be able to retrieve the sysdatabases info from SCOTTY) :
select * from linkedserver.master.dbo.sysdatabases
7.) Testing the double hop from SPOCK machine :
I logged into the SPOCK machine with FARUKCORP\spockuser, opened Management Studio and connected to KIRK SQL Server Database engine with current logged on user (FARUKCORP\spockuser)
Opened a query windows and executed the SQL Statement below :
select * from linkedserver.master.dbo.sysdatabases
and successfully received the results from the SPOCK machine.
“UDL Test” on a 64 bit machine
Let’s say you’ve started playing with UDL files on your "32 bit" machine and this tiny little UDL files helped you to identify connectivity issues or you’ve easily obtained the OLE DB connection strings for your applications. So far so good. But one day, you followed the same procedure on a 64 bit machine and couldn’t see the OLE DB Providers though you’re sure that it’s been installed. There must be something wrong then ?
The reason behind for this is simple. When you double clicked on a UDL file on a 64 bit machine, it’ll enumerate only the 64 bit OLE DB Providers and most probably you’ve installed a 32 bit OLE DB Provider.
So then, there should be a way of making UDL Test using the 32 bit OLE DB Providers on a 64 bit machine.
Actually when you’ve created a UDL file on a 64 bit machine and double clicked on it,
"C:\Program Files\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\test.udl
command will be called through C:\windows\system32\rundll32.exe
Both binaries (oledb32.dll and rundll32.exe) used here are 64 bit and 64 bit oledb32.dll does not deal with 32 bit OLE DB Providers.
Since we already have 32 bit versions of those oledb32.dll and rundll32.exe in other folders on our 64 bit machines, we need to use them. Therefore, instead of double clicking on the UDL file, we’ll need to execute the command below from a command line or Start/Run :
C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\test.udl
Et voila :) There you have your 32 bit OLE DB Providers.
When an application failed with a database/datasource connectivity problem, the first thing to check is if the box really cannot connect to the database/datasource using a "generic" way. The simplest technique is "UDL Test" using the OLE DB providers installed on the box . For example, if the application is failing to connect to a SQL Server 2000 or 2005, jsut follow the steps below :
1) Create a new empty text file like "test.txt" (For example, right mouse click on an empty place on your desktop, select "New" and "Text Document")
2) Rename the file as "test.udl"
As soon as you renamed the file, text file icon 
should change to a UDL icon 
3) Double click on test.udl file and you'll receive a window titled "Data Link Properties"
You'll get all the installed OLE DB Providers on the box when you've switched to the Provider tab. If the OLE DB Provider for the database/datasource you're interested in is in the list, select the OLE DB Provider and click on "Next"and you'll switch to "Connection" tab and will be ready to play with the OLE DB Provider. The rest depends on the OLE DB Provider you selected.
For example if you selected "Microsoft OLE DB Provider for SQL" , you'll see a window like that :
You'll need the name of the SQL Server (or instance name) you're trying to connect to the textbox in the 1st part. "Use a specific user name and password" is selected by default in the 2nd part and this means "SQL Authentication". You'll need to enter a SQL Server username here. If you select "Use Windows NT Integrated security" radio button, then the Username and Password textboxes will be disabled and the credential that you logged on to the machine will be used while connecting to SQL Server. Be sure that your SQL Server is in "mixed" mode (Please refer to SQL Server Books Online for the details )
You can either click on "Test Connection" directly to test the connection or click on the combobox to enumerate the available databases/catalogs on the server.
If you click on "Test Connection", you should receive "Test connection succeeded" message if you can connect to the SQL Server :
If you click on "OK" and click "OK" again, the information that you entered will be saved into the test.udl file. The UDL file is a simple text file and the magic line is the last line which is the "Connection string" Here's an axample for a SQL Server OLE DB Provider scenario when "Use Windows NT Integrated security" selected :
=============TEST.UDL file BEGIN=========
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME
=============TEST.UDL file END===========
Having the connection string from a UDL file
1) Will prevent you to make typos in the connection string (You're going to be sure that the connection string is right and working, cause you've got "Test connection succeeded" ;) )
2) Will help you to "generate" the connection strings which are using different types of OLE DB Provider that you're not familiar with their syntax. The parameter/value pairs are specific to the OLE DB Provider manufacturer. Of course, all OLE DB connection strings should have Provider=SOMEABBREVIATONFORTHEOLEDBPROVIDER pair (I can assure you that you'll never see that long "abbreviation" for an OLE DB Provider :o) )
