Post reply

How can the domain of a SQL Server be determined

  • September 9, 2009 at 12:45 pm

    #141509

    Hi,

    Scenario: Writing SSIS Logging to Text Logging Provider -> Text file destination: Server A -> SSIS Package executed through SQL Job on Server B -> Server Information at Hand: UNC Path such as defgh4997

    Constraint: Access to file destination denied

    Question: How can the domain the server belongs to be determined???

    Thanks for any hint

  • September 9, 2009 at 12:54 pm

    #1051362

    Environment variable:

    USERDOMAIN

    CEWII

  • September 9, 2009 at 1:48 pm

    #1051423

    Thanks for the hint

    Can you also please elaborate on how to use this environment variable

    Thanks

  • September 9, 2009 at 1:56 pm

    #1051426

    SET NOCOUNT ON

    DECLARE @Domain varchar(30)

    CREATE TABLE #cmdshelloutput ( outtext varchar(255) )

    INSERT #cmdshelloutput EXEC master.dbo.xp_cmdshell 'SET'

    SELECT @Domain = RIGHT(outtext, LEN( outtext ) - 11 )

    FROM #cmdshelloutput

    WHERE outtext LIKE 'USERDOMAIN=%'

    SELECT 'Domain: ' + @Domain

    GO

    DROP TABLE #cmdshelloutput

    GO

    CEWII

  • September 9, 2009 at 3:43 pm

    #1051468

    Elliott W (9/9/2009)

    SET NOCOUNT ON

    DECLARE @Domain varchar(30)

    CREATE TABLE #cmdshelloutput ( outtext varchar(255) )

    INSERT #cmdshelloutput EXEC master.dbo.xp_cmdshell 'SET'

    SELECT @Domain = RIGHT(outtext, LEN( outtext ) - 11 )

    FROM #cmdshelloutput

    WHERE outtext LIKE 'USERDOMAIN=%'

    SELECT 'Domain: ' + @Domain

    GO

    DROP TABLE #cmdshelloutput

    GO

    CEWII

    Keep in mind that in SQL 2005 xp_cmdshell will have to be enabled in surface area configuration.

    Before enabling xp_cmdshell, you may want to consider the impact and corporate policies regarding it.

    Alternatives:

    Ping the server

    or

    nbtstat -A xxx.xxx.xxx.xxx (where xxx.xxx.xxx.xxx is the IP Address).

    Both of these methods will show you the domain of the server in one form or another.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 9, 2009 at 3:44 pm

    #1051470

    I also forgot to mention another method:

    Ask the Engineering, Network, Server Admin team (whatever they are called at your work) what the domain for that server is.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 9, 2009 at 3:55 pm

    #1051476

    Thank you very much for the detailed information

    "I owe you guys"

  • September 9, 2009 at 10:54 pm

    #1051561

    Why can't we try this?

    Execute xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\','Domain'

    perhaps, the path changes from OS to OS. But the above should work on windows XP, 2000,2003.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • September 10, 2009 at 9:03 am

    #1051769

    Hi Vishal,

    Thank you for your contribution.

    It was very interesting to learn where in the registry the network settings can be found

    Since xp_regread is not referenced in SQL Server Online Books, it was possible for me to find out on the internet that this extended procedure is undocumented.

    Do you know of any constraints executing ths proc?

    Greetings,

    Mike

  • September 10, 2009 at 12:19 pm

    #1051871

    I believe you have to have high level permissions, such as sysadmin.

    CEWII

  • September 10, 2009 at 3:47 pm

    #1052015

    You can also use ole automation, see below, picked the script snippet up somewhere, can't remember where though

    --SQL

    declare @DomainName varchar(128)

    declare @hr int

    declare @sysInfo int

    --create a package object...

    exec @hr = sp_OACreate 'ADSystemInfo', @sysInfo output

    exec @hr = sp_OAGetProperty @sysInfo, 'DomainShortName', @DomainName output

    select @DomainName

    Andrew

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply