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
>
>

connection string question

I've been reading that I can't do this so I'm hoping someone smarter than I
can prove me wrong. Here is the scenario:
I have a SQL 2005 Report server running joined to Domain A.
I have a SQL 2000 server running on a workgroup machine with Windows Only
authentication enabled.
No trust can be established and the SQL 2000 box can't be switched to Mixed
mode authentication.
Can I create a connection string that would pass Windows creds instead of
SQL creds?
Thanks in advance for any advice!No. Windows credential of the Reporting Services box cannot be authenticated
by the SQL Server2000 box in this case. The only way is enable SQL
Server2000's mixed security mode and use SQL Server login username/password.
End of story.
"Zarborg" <Zarborg@.discussions.microsoft.com> wrote in message
news:4849EED4-788E-4EA3-A1DA-B764C13DC711@.microsoft.com...
> I've been reading that I can't do this so I'm hoping someone smarter than
> I
> can prove me wrong. Here is the scenario:
> I have a SQL 2005 Report server running joined to Domain A.
> I have a SQL 2000 server running on a workgroup machine with Windows Only
> authentication enabled.
> No trust can be established and the SQL 2000 box can't be switched to
> Mixed
> mode authentication.
> Can I create a connection string that would pass Windows creds instead of
> SQL creds?
> Thanks in advance for any advice!|||Poop. That's what I was reading. Thanks for the crushing blow to my
project! ;-)
"Norman Yuan" wrote:
> No. Windows credential of the Reporting Services box cannot be authenticated
> by the SQL Server2000 box in this case. The only way is enable SQL
> Server2000's mixed security mode and use SQL Server login username/password.
> End of story.
> "Zarborg" <Zarborg@.discussions.microsoft.com> wrote in message
> news:4849EED4-788E-4EA3-A1DA-B764C13DC711@.microsoft.com...
> > I've been reading that I can't do this so I'm hoping someone smarter than
> > I
> > can prove me wrong. Here is the scenario:
> > I have a SQL 2005 Report server running joined to Domain A.
> > I have a SQL 2000 server running on a workgroup machine with Windows Only
> > authentication enabled.
> > No trust can be established and the SQL 2000 box can't be switched to
> > Mixed
> > mode authentication.
> >
> > Can I create a connection string that would pass Windows creds instead of
> > SQL creds?
> >
> > Thanks in advance for any advice!
>