Home » 2013 » January

Monthly Archives: January 2013

How to find/modify SQLServer Agent logfile location?


1. To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out

DECLARE @AGENT_ERRORLOG NVARCHAR(255)

EXECUTE MASTER.DBO.XP_INSTANCE_REGREAD N’HKEY_LOCAL_MACHINE’,

N’SOFTWAREMICROSOFTMSSQLSERVERSQLSERVERAGENT’,

N’ERRORLOGFILE’,

@AGENT_ERRORLOG OUTPUT,

N’NO_OUTPUT’

SELECT @@SERVERNAME SERVERNAME, @AGENT_ERRORLOG AGENTERRORLOGLOCATION

This command will work for both default & named instance.

2. To modify location and name of SQLServer Agent log file

USE [MSDB]

GO

EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N’C:TEMPSQLAGENT.OUT’

GO

3. To recycle SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG

When SQLServer Agent recycles the log file, SQLAGENT.out will be SQLAGENT.1 & SQLAGENT.1 will be SQLAGENT.2 and so on.

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server Silent unattended installation using configuration file


1) Open Installation Wizard

Open Installation Wizard

Open Installation Wizard

2) Select Feature You want to install

Select Feature You want to install

Select Feature You want to install

3) Provide installation configuration

Provide installation configuration

Provide installation configuration

4) Now that you have the configuration file, copy it to your own folder or network share where you want to start the unattended installation.

Configuration file

Configuration file

5) Cancel setup as we are interested in the unattended silent mode of installation; not the UI one.

6) Edit the configuration file as follows:

  1. Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface

i. QUIET="True"

  1. Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My user instead of BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.

i. SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS"

  1. Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.
  1. Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.

i. IACCEPTSQLSERVERLICENSETERMS="True

  1. Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.
  1. Remove the UIMODE parameter as it can’t be used with the QUITE parameter.
  1. Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters if you want to install on the default installation directories or mention appropriate directories for installation.
  1. You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.
  1. The full list of available feature parameters and their descriptions : http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature
  1. Now, Your configuration file is ready, you need to create a batch file that will run the silent unattended setup. Create a new file ”SQLServer2012_SilentInstall” with extension = “.bat”.

We have added, Date time to get the time taken by complete installation. Edit below script with your setup & configuration file location.

@ECHO offecho Installing SQL Server 2008 R2date/ttime /t

“D:SQLFULL_x86_ENUsetup.exe” /ConfigurationFile=”D:ConfigurationFile.ini”

date/t

time /t

7) My SQL Server got installed

SQL Server got installed

SQL Server got installed

8) Let’s Verify

SQL Server Installed

SQL Server Installed

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 2 for SQL Server 2012 Service Pack 1 Is Now Available !


The 2nd cumulative update release for SQL Server 2012 Service Pack 1 is now available. Cumulative Update 2 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU2 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

KB Article For CU2 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles of SQL Server 2012 SP1

SQL Server 2012 SP1 Cumulative Update #2 has also two new improvements. For more details reference this blog

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Cumulative Update – 9 for SQL Server 2008 Service Pack 3 Is Now Available !


The 9th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 9 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU9 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU9 of SQL Server 2008 SP3

Previous Cumulative Update KB Articles of SQL Server 2008 SP3:

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server Reserve 1 user connection for DAC (Dedicated Administrator Connection)……want to confirm ?


Sometime before, I was giving training on basics of SQL Server. One of attendee is having doubt & want to confirm that SQL Server really Reserve 1 user connection for DAC (Dedicated Administrator Connection).

So I decide to show a demo for the same. I have changed the no. of User Connections to 1 & then try to login. So below is the PROOF.

Steps Performed for Demo

1. User Connections Current settings

2. User Connections New settings. You need to restart SQL Services after this.

3. I Try to Login to SQL Server by SSMS & got Error. But Why ?

4. I Try to Login to SQL Server by SQLCMD & got Error. But Why ?

5. Reason of getting error at step 3 & 4 is DAC (Dedicated Administrator Connection). SQL Server reserve one user connection for DAC.

6. Rollback the changes using DAC & come back to previous settings. You need to restart SQL Services after this.

7. User Connections After Rollback

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

The server principal “xxxx” is not able to access the database “msdb” under the current security context.


Problem :

Yesterday Night, One of my team member called & report that some users are getting below error no. 1 while connection to SQL server & error no. 2 while trying to open Management folder.

Error 1 : Cannot display policy health state at the server level, because the user doesn’t have permission. Permission to access the msdb database is required for this feature to work correctly.

Cannot display policy health state at the server level, because the user doesn’t have permission

Cannot display policy health state at the server level, because the user doesn’t have permission

Error 2 : The server principal “xxxx” is not able to access the database “msdb” under the current security context. (Microsoft SQL Server, Error: 916)

The server principal is not able to access the database msdb under the current security context

The server principal is not able to access the database msdb under the current security context

Analysis & Resolution : -

Most suspicious thing was, some users are facing issue & some user are working fine. When I goes into depth, I have found that user have super rights on SQL Server & on MSDB are working fine.

That means, it is clearly a permission issue but it is effecting random users in bulk.

Reason in my case : CONNECT permissions are denied from PUBLIC role.

Query to check CONNECT permissions :-

USE MSDB

GO

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,

DP.PRINCIPAL_ID,

DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,

P.CLASS_DESC,

OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,

P.PERMISSION_NAME,

P.STATE_DESC AS PERMISSION_STATE_DESC

FROM SYS.DATABASE_PERMISSIONS P

INNER JOIN SYS.DATABASE_PRINCIPALS DP

ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID

WHERE P.STATE_DESC = ‘DENY’

We have 2 possible solutions :-

1) Provide connect permissions to all user sepratly

2) Provide CONNECT permission to PUBLIC role

We have resolved the issue by running below command. Connect permissions was reestablished on PUBLIC role.

GRANT CONNECT TO PUBLIC

If you liked this post, do like on Facebook at https://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Under which user your SQL Agent Job run : SQL Agent Service Account or SQL Agent Job Owner ?


Today, I am willing to do demo that under which user my SQL Agent Job run, SQL Agent Service Account or SQL Agent Job Owner ?

Normally, We think its SQL Agent Service account user under which SQL jobs runs. But it’s not true in all cases, It depend on your Job owner permissions.

If the job owner is an account that is in the sysadmin fixed server role, than your step of job will be executed under the SQL Agent Service Account.

Otherwise, It will be executed by the account set as the job owner, no matter who is starting the job.

Exception : The job step is a CmdExec or ActiveXScript job step

If the job owner is an account that is in the sysadmin fixed server role, than your step of job will be executed under the SQL Agent Service Account.

Otherwise the step of job will be executed under the security account of the Proxy Account if enabled and configured.

If that proxy account is not configured the step will fail with Below Error.

Non-SysAdmins have been denied permission to run CmdExec job steps without a proxy account. The step failed.

Test Job run under SQL Agent Service account when Job owner is ‘sa’

Test Job run under Job Owner ‘test’ user when Job owner is ‘test’ (non –sysadmin account)

Reference : Rohit Garg (http://mssqlfun.com/)

Follow

Get every new post delivered to your Inbox.

Join 210 other followers