Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

Fix/Solution/Workaround:

Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”
to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

Run following script in SQL Analyzer to change the authentication

ALTER LOGIN sa ENABLE
GO
ALTER LOGIN sa WITH PASSWORD = '<password>'
GO

OR

In Object Explorer, expand Security, expand login, right-click on sa, and then click Properties. On the General page, you may have to create and confirm a password for the sa login. On the Status page, in the Login section, click Enabled, and then click OK.

Using the Alias name instead of IP address/Host Name/Instance Name for accessing the SQL Server

When working on Support and Production DBA there is the possibility we need to access the multiple SQL Server Instance. Most of the time we will be using the IP address/Host Name/Instance Name. Just thought of checking whether the SQL Server is allowing us to use the Alias name instead of using the IP address. It would be very helpful and we can access the server with our own creative and easy accessible name.

Especially when you are in support (or) managing multiple server it would be easy to remember the Alias name instead of IP address.

Steps:

• Click on SQL Server Configuration Manager
• Then Click on Alias Name

• Right Click and add new Alias

 

• Enter the following info and click Ok.
• Alias Name (The Name you would like to keep for that IP address/Host Name/Instance Name)
• Post No (Default 1433) - (OR) Provide the Port number which used was used in Server.
• Protocol – TCP/IP
• Server – (It can be Actual IP address/Host Name/Instance Name)

• Now you can access the Database Server using the New Alias Name in SQL Server Management Studio.

Thanks & Regards,

Karthikeyan Jothi.

"You Never Fail Until You Stop Trying"

Get the file name from given path

Get the file name from given path

Steps:

·         Copy the above below script and Execute the full script....

·         Run the below given script

Execute:
SELECT dbo.UDF_GET_SPECIFIC_STRING('c:\testing\name.txt')

-- The output will be "name.txt"

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'UDF_GET_FILE_NAME_FROM_GIVEN_PATH' AND TYPE = 'FN')
    DROP FUNCTION UDF_GET_FILE_NAME_FROM_GIVEN_PATH
GO

CREATE FUNCTION [dbo].[UDF_GET_FILE_NAME_FROM_GIVEN_PATH]
(@PS_String VARCHAR(1000))
RETURNS VARCHAR(256)
AS
/*       
----------------------------------------------------------------------------------------------------------------------------------------------------------------
    Description            :
                       Get the file name from given path
                     
    Input Parameter        : TEXT
                         
                 
    Return Value        : String

    --IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'UDF_GET_FILE_NAME_FROM_GIVEN_PATH' AND TYPE = 'FN')
    --    DROP FUNCTION UDF_GET_FILE_NAME_FROM_GIVEN_PATH
    --GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------
    --Created By    : Karthikeyan Jothi
    --Created On    : 1/10/2014

    --USP : User Defined Function

    --- Changes Details
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    --S.no    Modified By            Modified On        Reason
    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    -- Execution Part
    --====================
        Run the UDF with different test values
        SELECT dbo.UDF_GET_FILE_NAME_FROM_GIVEN_PATH('') AS 'EmptyString';
        O/P -> NULL
     
        SELECT dbo.UDF_GET_FILE_NAME_FROM_GIVEN_PATH('c:\testing\name.txt')
        O/P -> name.txt
     
        SELECT dbo.UDF_GET_FILE_NAME_FROM_GIVEN_PATH('c:\testing\tetsubfdsasbakjskanbsakbskas\eg.txt')
        O/p -> eg.txt
 
    --***********************************--
----------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
BEGIN
Declare     @output varchar(256)

    -- Best Way...
    IF LEN(@PS_String)> 0
    BEGIN
    SET @output = RIGHT( @PS_String, CHARINDEX( '\', REVERSE( @PS_String)) - 1)
    END
  
    return @output
END-------------------------------------------------------------------------------------------------------------------------

Thanks & Regards,

Karthikeyan Jothi.

"You Never Fail Until You Stop Trying"

Add multiple columns to all the tables in the database.

Add multiple columns to all the tables in the database.

Script:

select 'Alter Table ' + TABLE_NAME + ' ADD '
        + ' Column_Name1 Datatype  ,'
        + ' Column_Name1 Datatype  '
        + CHAR(10) + ' GO '
from INFORMATION_SCHEMA.TABLES

Steps:

• Select the database and Copy the about given Script
• Select the Result in Text View
• Run the script
• Copy the Script and Execute it.

Thanks & Regards,

Karthikeyan Jothi.

"You Never Fail Until You Stop Trying"

Cannot create a connection to data source Data source for shared dataset. Login failed for user NT AUTHORITY\ANONYMOUS LOGON

Issue:

While Configuring the SSRS

• Reporting Database in Report Database Server A
• SSRS was configured and accessed the Report Database Server A from Report Server B.
• Accessed the Server using the windows authentication the same windows AD account was used across all the server.
• The Report Server B is configured and it was accessing Report Database Server A without any issues.
• Using the (sql server business intelligence development studio) Created a new report in Report Server B and tested locally and it was working fine. It was checked with IE too.
• When I tried to access the Report from Other Machines which resides in the same domain received the following error.

----------------------------------------------------------------------------------------------------------------------------------

An error has occurred during report processing. (rsProcessingAborted)

The execution failed for the shared data set 'DataSet1'. (rsDataSetExecutionError)

Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

----------------------------------------------------------------------------------------------------------------------------------

• We tried to do all kind of changes on the Windows user account but it got failed.

Solution:

• Created new User with "SQL Server Authentication" and assigned to the Database (Read only) permission.
• Modified the Data Source Configuration in BIDS at (Report Server B) and updated with SQL Server User Authentication
• Rebuild and Deployed the reports
• The reports are started working perfectly and we were able to access the Reports from all the machines.

Hope this will be helpful…

Thanks & Regards,

Karthikeyan Jothi.

"You Never Fail Until You Stop Trying"