Category: Uncategorized

xp_cmdshell raises error “a call to LogonUserW failed with error code 1385”

An error occurred during the execution of xp_cmdshell. A call to ‘LogonUserW’ failed with error code: ‘1385’.

In order to fix this you need to open the Local Security Settings on the host machine.
 
Navigate to Security Settings -> Local Policies -> User Rights Assignment.
 
Open “Log on as a batch job” and add the user assigned as the xp_cmdshell proxy account

A multi-subnet FCI by default enables the RegisterAllProvidersIP cluster resource for its network name. In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur. For more information, see AlwaysOn Client Connectivity (SQL Server) and Create or Configure an Availability Group Listener (SQL Server).

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.
The default client connection time-out period for SQL Server Management Studio and sqlcmd is 15 seconds.

https://msdn.microsoft.com/en-us/library/ff878716(v=sql.110).aspx

— in the case where a server has been cloned or renamed 
— and @@SERVERNAME still returns name of old server

use master
go

SELECT @@SERVERNAME, SERVERPROPERTY(‘MachineName’)
GO

SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

sp_dropserver ‘OLDSERVEROLDINSTANCE’;
GO
sp_addserver ‘NEWSERVERNEWINSTANCE’, local;
GO

— has been updated in servers table
SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

— may need to restart instance 
— for it to take effect for @@SERVERNAME

SELECT @@SERVERNAME, SERVERPROPERTY(‘MachineName’)
GO

Download MSDE 2000 Release A

This will download a compress executable file MSDE2000A which is SQL Server 2000 Desktop Engine with SP3a (8.00.760)

Executing this will expand to a folder C:MSDERalA

From a command line navigate to this folder and execute on of the following:

To install a default instance configured to use the default Windows Authentication Mode, just pass in the SAPWD=”AStrongSAPwd” switch where AStrongSAPwd is a strong password for the “sa” account.

C:MSDERalA>setup SAPWD=”AStrongSAPwd”

To install a named instance include the INSTANCENAME=”InstanceName” switch.  To use mixed mode Windows Authentication and SQL authentication use the SECURITYMODE=SQL switch.

C:MSDERalA>setup INSTANCENAME=”MyInstance” SECURITYMODE=SQL SAPWD=”AStrongSAPwd”

To start SQL Server, go to Start->Run and type sqlmangr.exe to bring up the sql server service manager.  You can start the service using it and set it to start when OS starts.

Now open a command prompt and attempt connecting using osql

C:>osql -E

Resources:
How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)
https://support.microsoft.com/en-us/kb/324998
http://support.microsoft.com/default.aspx?scid=kb;en-us;810826
http://support.microsoft.com/kb/322336/EN-US/

To find .NET Framework versions by viewing the registry (.NET Framework 1-4)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftNET Framework SetupNDP

To find .NET Framework versions by viewing the registry (.NET Framework 4.5 and later)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftNET Framework SetupNDPv4Full

https://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx#net_a

    SELECT A.restore_history_id
         , A.restore_date  –date database was restored
         , A.destination_database_name
         , A.user_name
         , A.restore_type
         , B.backup_finish_date –date of backup used to restore database
         , C.physical_device_name  –backup file used to restore database
         , A.stop_at
         , A.stop_at_mark_name
         , A.stop_before
      FROM msdb.dbo.restorehistory A
 LEFT JOIN msdb.[dbo].[backupset] B
        ON A.backup_set_id = B.backup_set_id
 LEFT JOIN msdb.[dbo].[backupmediafamily] C
        ON B.[media_set_id] = C.[media_set_id]
     WHERE A.restore_date 
         = ( SELECT MAX(A1.restore_date)
               FROM msdb.dbo.restorehistory A1
              WHERE A1.destination_database_name = A.destination_database_name )

1. Open regedit.exe

2. Export copy of the registry

3. Navigate to

HKEY_CLASSES_ROOTMicrosoft.PowerShellScript.1Shell

4.  Change the default from ‘Open’ to ‘0’

5. Could change default parameters to powershell.exe in HKEY_CLASSES_ROOTMicrosoft.PowerShellScript.1ShellCommand

Another option:
3. Navigate in registry to HKEY_CLASSES_ROOTMicrosoft.PowerShellScript.1ShellOpenCommand

4. Change Old Value:
“C:WindowsSystem32notepad.exe” “%1”

to New Value:
“C:WindowsSystem32WindowsPowerShellv1.0powershell.exe” -NoLogo -NoProfile -NonInteractive -ExecutionPolicy “Bypass” -File “%1”

Why not do this:

This makes it easy for dangerous .ps1 files to be executed.  With great power comes great responsibility.

Problem: Error encountered during installation of vendor software package that includes creation of database

Symptoms: Error message similar to “37000-1803: [Microsoft] [ODBC SQL Server Driver] [SQL Server] The CREATE DATABASE statement failed. The primary file must be at least XXXX MB to accommodate a copy of the model database.”

Cause: Vendor has specified a database size in their create database script that is smaller than the size of the “model” database on that instance.

Solution: Shrink model database