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

Question

Hello,I want to set environment variable in sql server process running on windows machine. Please help me on this

olafhelper · Answer

For what?SQL Server runs under a service account, which do't have a profil and so no enviroment.

joicejoy · Answer

olafhelperThanks for the quick reply.I have two instances of sql server running on two different ports on the same machine. I want my java application to dynamically get these ports from process environment, but you are saying there is no environment right?

olafhelper · Answer

SQL Server don't use Java, so how does you processes look like?

lainrobertson · Answer

joicejoy&nbsp;&nbsp;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).&nbsp;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.&nbsp;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.&nbsp;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).&nbsp;I do have to say though - as a disclaimer, while this is related to SQL Server, I'd put it in the "barely" category.&nbsp;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.&nbsp;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:&nbsp;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") { "&lt;All&gt;" } else { $Socket.GetValue("IpAddress"); }
                    Port = $Socket.GetValue("TcpPort");
                    Active = "1" -eq $Socket.GetValue("Active");
                }
            }
    }
}
else
{
    Write-Error "No SQL Server installation detected.";
}&nbsp;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.&nbsp;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.)&nbsp;As an alternative, you can shell out and call the SQL PowerShell module to obtain the same data if that's your preference.&nbsp;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.&nbsp;Cheers,Lain

joicejoy · Answer

LainRobertson&nbsp;Thanks for your replyMy requirement is just to put one environment variable in the sql server process for example "MY_SQL_SERVER_PORT=1434"&nbsp;In linux processes we can find the process environment variables under "/proc/1475/environ" .Similar way windows should have something right? I want to set a custom environment variable there.Is that possible in sql server process which is running on windows machine?

Forum Discussion

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

Share

Resources