World Search

Tuesday, April 29, 2014

Reset SA Password For Multiple Instance

Power Shell Script  :-

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
Foreach ($list In $(Get-Content -Path p:\serverList.txt)) {
   $server = $list.split(":")
   $srvObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server[0]
   $srvObject.Logins.Item('sa').ChangePassword($server[1])
   $srvObject.Logins.Item('sa').Alter()
}



The above uses the file "ServerList.txt", where we neeed to store the instance names and corresponding password in the following pattern,

[Instance Name]:{PASSWORD}
[Instance Name2]:{PASSWORD}
......




Tuesday, April 22, 2014

no connection could be made because the target machine actively refused SQL Server OLAP

ERROR :- No connection could be made because the target machine actively refused it sql server olap

Event Viewer :- Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn't contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc.


Solution :- 
 
The only supported way to connect to a clustered SSAS instance is to use the “virtual server name” (also called network name). Usage of the instance name like in  “VirtualServerName\InstanceName” is not supported! One reason being that all clustered instances will always run on port 2383 and thus we don’t need to specify the instance name. The other reason being that the SSAS part of SQL Browser service functionality (OLAP redirector service) is not cluster-aware and we cannot guarantee that the content of “msmdredir.ini” is always updated correctly.
In this context “not supported” does not mean that connectivity via instance name does not work. In fact it usually does work. But we cannot guarantee that it will always work in a stable fashion. Therefore our strong recommendation is to always use “VirtualServerName” (=Network name) for establishing connections to a clustered SSAS instance.



More Info :- http://blogs.msdn.com/b/as_emea/archive/2014/01/08/connecting-to-clustered-ssas-instances.aspx