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

I am using Dreamweaver 8 on Windows XP. I am trying to connect to SQL Server 2005. My connection string isn't right. I ran the OBDC test and it works so I know its the connection string. My webpages give me the error that the server does not exist or access denied. When I use anything else like SQLNCLI.1 for the provider it gives me the error that the provider is not found. What should the connection string consist of and what should I use for Provider?

MYCONN_Conn ="Provider= SQLOLEDB;Data Source=sql2005.loc.mss.edu;Integrated Security=SSPI;Initial Catalog=OPENAssessment;"

Thanks in advance.

instead of giving server namesql2005.loc.mss.edugive actual IP address of the server. This should fix

|||

Here's an example connection string taken from one of my live web apps:

<addname="ConnectionString"connectionString="Data Source=xxxxxxx;Initial Catalog=xxxxxxx;UID=xxxxxxx;PWD=xxxxxxx"providerName="System.Data.SqlClient" />

For the data source in test we use the machine name, in production, we use the IP address (xxx.xxx.xxx.xxx) of the production Sql Server.