October 3, 2014 at 1:15 pm
My company uses Environment Variables to store variable values for SSIS. These values are stored on a table on all of our servers, and by in large this set up works very well.
Someone created a couple of .vbs scripts to populate the Environment Variables so that we can quick deploy to a server, and also so that we can use our local machine to emulate a server during development. So far I have run the scripts on a couple of the development servers, and have had no issues. I just tried to run the script on my local machine, and I get the following error:
D:\<full file path withheld>\EnvironmentVariables_sub.vbs(42, 2) Microsoft OLE DB Provider for SQL Server:[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
I have looked into the error message, and have found a few ideas:
http://support.microsoft.com/kb/888228/en-us
Many of the other pages I have found have the same links as these 2.
So far
* I have tried enabling both the TCP/IP and Named Pipes for my local SQL Server instance. I then stopped and started my local SQL Server instance.
* I have fully disabled my local SQL Server Express to insure that was not interfering with the connection.
* I have verified my local Aliases are set as expected by re-running the scripts that set both my 32-bit and 64-bit alias configurations scripts.
* I also edited the .vbs script to see what connection string was being passed. I just did a wscript.Echo, and have copied almost the entire string below. The sections in between the <> are my edits, there are no <> in the connection string:
Provider=sqloledb;Data Source=<Alias name withheld>;Initial Catalog=<catalog value withheld>;Integrated Security=SSPI
I will be honest; the Microsoft KB link (link 2) does not fully make sense. I have focused almost exclusively on SQL development for the past decade, so I am not as familiar with passing connection strings. Even still, it seems like I am passing the values that the KB talks about.
In short, I am stumped. Does anyone else have any ideas? Tag you're in...
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
October 3, 2014 at 2:02 pm
I found the solution. In case you ever have to troubleshoot this for your system, here are the steps I used to correct my specific issue.
Root Cause: Invalid alias address provided. Attempting to connect to Prod servers instead of Dev servers.
I used both 32-bit and 64-bit versions of cliconfg.exe (go to C:\, and search for the file). I verified the address listed on the explicit server name did not match the aliased server name. I used SQL Server Configuration Manager to edit the Server Address and Port to make sure the alias was pointing to the correct location. I then stopped and started the services (I am not sure if that is required, but that is what I did), and was able to connect without an issue.
For easier detection of this issue just open SSMS, and try to connect to the alias name instead of the explicit name. For instance:
Explicit server name - AchmeDevServer
Alias name - AchmeDev
If you cannot type AchmeDev into SSMS and bring up the correct server then your alias is pointing to the wrong location. Hopefully your environment is set up so that you will not have permissions to connect to you Prod server, and you will receive this error. God help me if I had connected to and run jobs against the Prod server - :pinch:.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply