Tuesday, February 9, 2016

Not able to login to SQL

Scenarios when this might happen:

You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:

You have removed any and all built-in administrators account from SQL Server logins
You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
You have set the password of SA to something extremely complex which is hard to remember.
For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn’t have SYSADMIN privileges.
Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.

What would you do now?

Some quick options I can think of are listed below:

1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)

2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn’t guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don’t remember the SA password, restoring the Master database will not help you and you are back to square one.

3. You will call up Microsoft PSS

You are now running out of options. What would you do?

There’s a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Here are the steps you will need to perform:

1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)

From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)



Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.

(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)

2. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.

SQLCMD –S <Server_Name\Instance_Name>

You will now be logged in to SQL Server as an Admin.

3. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.

To create a new login and add that login to SYSADMIN server role:

1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

2> go

1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'

2>go

To add an existing login to SYSADMIN server role, execute the following:

1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.

4. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

Note: Those that might be thinking this might make it easy for anyone to get access to SQL Server, well remember that you do have Auditing and will have control of who gets access to the local servers administrators group. If you haven't enable controls at that level then you may have bigger security issues in hand!!!

Credit: http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx

SQL single user mode (DAC: Dedicated Administrator Connection)

If you are stuck with SQL Single server connection mode then you can use DAC (Dedicated Administrator Connection) to the rescue:
  1. Go to New Query.
  2. Type the following into the Server Name dialog box: ADMIN:ServerName\InstanceName (eg: ADMIN:MSSQL\SQLDEV).
  3. Now you can run any scripts needed against your database in Single User Mode.

System restart from previous installation or update is pending

Today while installing Sharepoint 2013 Foundation in our web server I faced an error which says: 
System restart from previous installation or update is pending. Restart your computer and run setup to contiue.


I tried restarting but it didn't work and I understood that it's a trap so started what I am good at "Googling" for solution. Here it is:

  • Open the Registry editor (run > regedit)
  • Navigate to this path “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager“
  • Rename the “PendingFileRenameOperations” value to “OldPendingFileRenameOperations“

Note: Don't reboot your machine after this registry hack because if you do then it will again create a key named "PendingFileRenameOperations" and you will be stuck in this vicious circle.

Credit: https://social.technet.microsoft.com/Forums/office/en-US/f29b066e-9cd5-4a71-9db1-77ba01e40173/a-system-restart-from-a-previous-installation-or-update-is-pending?forum=sharepointadmin

Saturday, February 6, 2016

Hide parameters in Report dialog Ax2012

There are different ways to do it. Some of them are:



Method 1:

Why the default ranges will come in Dialog?:

Because in the Query used it will show the index fields of the Table, so to hide the Default ranges you have to overload the showQueryValues() in the Controller class: return FALSE.

Credit: http://axdevhelper.blogspot.com/2012/09/how-to-hide-default-query-parameters-in.html


Method 2:

The View group fields comes from InventDimViewContract class which is added as a parm method in ProdPickListContract class to show the options for all inventory dimensions.

Suppose, we want to hide all the parameter groups from report dialog and just want to display the dynamics filters on the report. You need to modify the UIBuilder class of your report. For production picking list report, the UI Builder class is ProdPickListUIBuilder. The build method needs to be modified and the code in Bold below can be used to hide the whole parameter group from SSRS report dialog:

public void build()
{
FormBuildGroupControl grp;
grp = this.dialog().curFormGroup();
grp.frameType();
grp.columns(2);

if (this.controller().parmArgs().menuitemName() == #yourMenuItemName)
{
grp.visible(false);
}
super ();
}

To hide the dynamic filters from SSRS report dialog based on caller menu item, you need to override the following method in your controller class and return false from this method based on your condition:

showQueryValues

Override this method in your controller class and write the following code:

public boolean showQueryValues(str parameterName)
{
If (this.parmArgs().menuItemName() == menuItemOutputStr(#YourMenuItemName)
{
return false;
}
else
{
return true;
}
}

Credits: https://syedbaber.wordpress.com/2015/07/23/hiding-parameter-groups-or-dynamics-filters-on-ssrs-report-dialog-at-runtime/

Method 3:
If you use a report controler class, you can overwrite the showQuerySelectButton method and return false.


Method 4 :
If you remove the ranges from the modeled query, and add them back programmatically in the methods of the query, they also will not show up as parameters for the report.


Method 5:

contract parameters are can definately be hidden . Look at any report that uses srstmpTableMarshaller class (Pseudo tmp table report's ). or any document reports . Journal record id is generally passed from controller --> Design (via parameter value in contract class) -- >DP class.


But still it's not visible on the report. All you need to is set its property to hidden in VS.
I feel its more like issue with the usage data.
Once you set the property to hidden you need delete the report from report manager and then redeploy, remove usage data, delete records in SYSLastValue, SRSReportQuery restart Reporting services.

Method 6:

Allow dynamic filtering false and remove the dynamic parameter. redeploy the report and refresh the user cache.


Method 7:

You can also use a not defined group.
SysOperationGroupMemberAttribute(‘group_notdefined_at_classdeclaration’)

The group ‘group_notdefined….’ will not be shown on the dialog, so all its members are
invisible.

Credits: https://community.dynamics.com/ax/f/33/t/83017

https://community.dynamics.com/ax/f/33/p/155614/364546#364546


Now one of them would surely help :)

Friday, January 29, 2016

Finding out which tables were updated by an operation in AX

Today while searching for some issue I stopped at a very interesting and neat trick in AX. Using this trick you can get a list of tables which get updated while performing any operation. I am sharing the blog post as is here with the source:

At times when troubleshooting Microsoft Dynamics AX you need to know which tables were updated by a particular operation, for example after performing a posting. This could be if you want to be sure that all the right tables are updated correctly after an upgrade, if you have unexpected results on one customer/vendor etc or if you have made an update the system and you want to be sure that everything is ok.
It is possible to use the SQL trace for this, but for functional people it can be at times be a bit challenging to interpret these results, so here is a tip for how you can easily find out which tables were updated when performing an operation in AX. In this example, we will show the effect on tables from a Sales Invoice posting in Microsoft Dynamics AX 2012 as this is quite a complex process that affects many tables.
The whole technique here relies on using a standard report ‘Size of company accounts’ in a novel way. This report returns an output of all the tables in AX that have data in them. In this article, we will see how we can use this report to print a snapshot of the tables before and after an operation so that when we print it ‘after’ we will see which tables got data populated into them from an AX operation.


The steps are as follows
1.  Go to the AOT and locate the Class ‘SysCompanySizeDP’ and open up the method called ‘insertIntoTempTable’. Change the highlighted function from ‘tableid2Pname’ to instead ‘tableid2name’. The reason we are changing the function is that it is easier to locate the tables in the AOT when the report prints the tables using the technical names. If we didn’t do this, the report would, for example, print the name of a table like ‘Customer Invoice Journal’ instead of ‘CustInvoiceJour’
2. Now go to System Administration\Reports\Database\Size of company accounts and run the report. Note the technical names

3. Export this report to Excel

4. Open up the saved Excel sheet and notice that the report content is copied over
5. Now let’s make a sales order invoice posting
6. We go back to System Administration and run the company size report again and once more save it, this time under a different name, for example ‘Company Size 2’

7. Now we copy the tables in the sheet from the last run ‘Company size report’ to the first saved Company size Excel sheet

So that we have a sheet with 2 different ‘Company size’ report executions – Before and after the Sales Order invoice posting

8. Now add a new column ‘Difference’ with a simple calculation extracting cell ‘C’ from cell ‘H’ and copy it across the rows as highlighted on
the screenshot in red below. Notice also the black box, that shows already some tables that have been affected

9. Now in order to easily identify which tables were affected by the posting, we can do this trick, apply a filter in the ‘Difference’ column
and unmark ‘blanks’ in the display of the cells’ values
RESULT
We can now see easily which tables were updated by the Sales Order posting. The same technique can naturally be used by any other kind of operation in AX, such as a Journal posting or a Master Planning run – all it relies on is just tracing which tables were updated since the last run of the report. Have fun!


Source: https://blogs.msdn.microsoft.com/axsupport/2013/09/27/finding-out-which-tables-were-updated-by-an-operation-in-ax/

Sunday, January 24, 2016

Unable to connect to the AOS specified in the AX Client Configuration in SSRS

Today, I faced a really weird issue while designing a report in VS2012. After designing the report I tried building the solution and got the below error:

Error 1 Unable to connect to the AOS specified in the AX Client Configuration. The configuration could be missing, invalid, or the AOS is not running. To connect to the AOS, check the network connection between the client and the AOS, verify that a valid configuration exists and that the AOS service is running on the server. C:\Program Files (x86)\MSBuild\Microsoft\DynamicsTools\Microsoft.Dynamics.Framework.Design.Reporting.Modeling.targets 103 6 ReportModel2



Error 2 The "GenerateRdlTask" task failed unexpectedly.

Microsoft.Dynamics.Framework.BusinessConnector.Session.Exceptions.NoKernelSessionException: Unable to connect to the AOS specified in the AX Client Configuration. The configuration could be missing, invalid, or the AOS is not running. To connect to the AOS, check the network connection between the client and the AOS, verify that a valid configuration exists and that the AOS service is running on the server. --->


After trying the obvious things like verifying the SSRS & BC configurations and connectivity, I had to move to the boss "Google" and got out a really interesting thing about how Business Connector works:


"The root cause is the SSRS report file size is exceeding the limit, as there are multiple layouts for the report file, and each layout has several pictures embedded. So, the size was over 10M, we tried multiple times, the report can work correctly when the size is relatively small, but once over about 10M, the business connector cannot hold the report effectively and the connect is lost.


The solution is to create another SSRS file to have other layouts."


I had created a copy of the existing design which was exceeding the size so I deleted it and all worked fine.




Source: http://dynamicsuser.net/forums/t/84427.aspx

Friday, January 22, 2016

SSRS performance improvement

With one of our clients, we were facing this issue of reports loading really slow. Even the simple document reports were taking 2-3minutes to display data. So finally the solution came out that we need to increase the recycle time of the SSRS.

That's how to do this:
Navigate to "C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer" (depending on the SSRS version).

Look for file RSReportServer.config and open it.

Now find the tag "RecycleTime" from the default "720" to "1440". You can try increasing the value further depending on the performance.


Hope this helps. :)