How to set environment variables for sql server process running on windows

joicejoy 

There are ways of using an environment variable, but there's not much value in speaking to them since that undermines what you asked for, which is a way of dynamically obtaining the endpoint configuration(s).

The endpoint configuration is not stored in any environment variables, meaning someone would have to manually add them to the system environment variables table (taking the easiest option), but of course, if SQL Server's reconfigured, or if it's configured to use dynamic ports and those change on a whim, then the whole manual environment variable approach is wasted and you're back to square one.

Java on Windows can read the registry, and the SQL Server endpoint is configured - and maintained - within the registry, meaning you can actually look up the endpoint configuration. But it's not as simple as looking up a key-value pair the way you probably expected to using the environment variable approach.

Rather, you have to look up a few registry keys that act as pointers to other keys, from which you can read the value(s).

I do have to say though - as a disclaimer, while this is related to SQL Server, I'd put it in the "barely" category.

Anyhow, I do have a PowerShell script I use for my own purposes (as I work a lot with Server Core) that lists the endpoint configuration. It's a lazy script insofar as I wrote it to be run locally, not remotely, but that's unimportant for your question.

PowerShell isn't hard to read so I'm sure you can figure out what's going on, but at a minimum, the only things you'd likely be interested in are the registry paths contained within:

if (Test-Path -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL")
{
    (Get-Item -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").Property |
    ForEach-Object {
        $InstanceName = $_;
        $InstallationName = (Get-Item -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").GetValue($InstanceName);
        $Instance = Get-Item -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstallationName";
        $Version = Get-Item -Path "$($Instance.PSPath)\Setup";

        $Sockets = Get-Item -Path "$($Instance.PSPath)\MSSQLServer\SuperSocketNetLib\Tcp";
        $ListenOnAll = "1" -eq $Sockets.GetValue("ListenOnAllIPs");

        (Get-Item -Path "$($Sockets.PSPath)\*").PSPath |
            ForEach-Object {
                $Socket = Get-Item -Path ($_);

                [PSCustomObject] @{
                    Version = $Version.GetValue("Version");
                    Edition = $Version.GetValue("EditionType");
                    Instance = $InstanceName;
                    ListenOnAll = $ListenOnAll;
                    Address = if ($Socket.PSChildName -eq "IPAll") { "<All>" } else { $Socket.GetValue("IpAddress"); }
                    Port = $Socket.GetValue("TcpPort");
                    Active = "1" -eq $Socket.GetValue("Active");
                }
            }
    }
}
else
{
    Write-Error "No SQL Server installation detected.";
}

If ListenOnAll = 1 then IPAll the only endpoint configuration you should use. Otherwise, if it's false (as it is on all my configurations), you'll need some logic to determine which of the other non-loopback endpoints you prefer to use.

I also never use a dynamic port configuration, so that's not reflected within my script. If you do use dynamic ports, you'll need to factor that in as well. (Like I said, it's a lazy script only I use.)

As an alternative, you can shell out and call the SQL PowerShell module to obtain the same data if that's your preference.

As a final note, this isn't a Java forum meaning this isn't likely to be the best place to ask for help in writing a Java solution for this task. We can give you tips on where to look for stuff but that's probably as far as we'll go.

Cheers,

Lain