Connection string security.

Howdy all.
Currently on 2000, but will be going to 2005 in the next couple of months.
We recently ran into a scenario where a developer built a back door into our
Production SQL box, and we are not too happy about it. Here is the scenario:
Since a lot of our apps are for the internet, we mostly use SQL Server
authentication for the app to talk to the DB. In our environment, developers
are NOT allowed to connect directly to our OLTP box, and as such dont have
the username or password for this connection. But one of them built in
funcationality to display the connection string (username and password
included) into his app. The DB connection string is stored in a web config
file, and he is simply querying that file. Is there anything I can do to
prevent this? Apps are written in both .NET and Java.
TIA, ChrisRIf your developer programmed some back door into the app, there's not much
you can do from that end, other than changing the app to use windows
authentication. You can do this without requiring users to log in
explicitly by setting the web site to anonymous authentication, but setting
the identity to a domain user that has been granted limited access to SQL
Server (ideally, the login will only have permissions to execute stored
procedures in certain database(s)).
However, what you should ensure is that the apps that talk to SQL Server do
so only through the internal network. The web app is visible to the world,
but the SQL Server box is not. You can use a mix of hardware/software
firewall appliances to accomplish this. If the developer is not inside your
network, then he/she cannot connect to SQL Server regardless of the
credentials they are trying to use.
A
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> Howdy all.
> Currently on 2000, but will be going to 2005 in the next couple of months.
> We recently ran into a scenario where a developer built a back door into
> our
> Production SQL box, and we are not too happy about it. Here is the
> scenario:
> Since a lot of our apps are for the internet, we mostly use SQL Server
> authentication for the app to talk to the DB. In our environment,
> developers
> are NOT allowed to connect directly to our OLTP box, and as such dont have
> the username or password for this connection. But one of them built in
> funcationality to display the connection string (username and password
> included) into his app. The DB connection string is stored in a web config
> file, and he is simply querying that file. Is there anything I can do to
> prevent this? Apps are written in both .NET and Java.
> TIA, ChrisR|||Thanks Aaron!
"Aaron Bertrand [SQL Server MVP]" wrote:
> If your developer programmed some back door into the app, there's not much
> you can do from that end, other than changing the app to use windows
> authentication. You can do this without requiring users to log in
> explicitly by setting the web site to anonymous authentication, but setting
> the identity to a domain user that has been granted limited access to SQL
> Server (ideally, the login will only have permissions to execute stored
> procedures in certain database(s)).
> However, what you should ensure is that the apps that talk to SQL Server do
> so only through the internal network. The web app is visible to the world,
> but the SQL Server box is not. You can use a mix of hardware/software
> firewall appliances to accomplish this. If the developer is not inside your
> network, then he/she cannot connect to SQL Server regardless of the
> credentials they are trying to use.
> A
>
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2D572DAA-2C92-46D9-8057-20C4B8A3304C@.microsoft.com...
> > Howdy all.
> >
> > Currently on 2000, but will be going to 2005 in the next couple of months.
> > We recently ran into a scenario where a developer built a back door into
> > our
> > Production SQL box, and we are not too happy about it. Here is the
> > scenario:
> >
> > Since a lot of our apps are for the internet, we mostly use SQL Server
> > authentication for the app to talk to the DB. In our environment,
> > developers
> > are NOT allowed to connect directly to our OLTP box, and as such dont have
> > the username or password for this connection. But one of them built in
> > funcationality to display the connection string (username and password
> > included) into his app. The DB connection string is stored in a web config
> > file, and he is simply querying that file. Is there anything I can do to
> > prevent this? Apps are written in both .NET and Java.
> >
> > TIA, ChrisR
>
>