Other ways to stop this from occuring?

SQL2K now, but will be going to 2005 shortly.
Howdy all. All of our apps (both inTRAnet and inTERNET) use SQL
authentication to connect to the DB's. I am well aware of the reasoning to
use WINNT authentication instead of SQL authentication, but this is currently
what we do. There are some folks on a mission to get this to change as they
want "single sign on" for their apps, but I am quite reluctant for the
following reason:
We have thousands of employees here. Currently, none of them can connect to
my OLTP server from outside the app and run their own ad-hoc queries/ DML/
etc. because their Windows account doesnâ't have permissions to do so and they
donâ't have the password to the SQL account that the app uses. I ever need to
worry the end user that decides to use MS Access to query huge amounts of
data to his remote location and causes blocking without realizing it. Nor do
I ever need to worry about the developer who likes to run his tests in Prod,
pegging the CPU, because his test environment doesnâ't contain all the data he
needs. These are scenarios Iâ've run into in other organizations. Like it or
not, SQL Authentication does provide these benefits. I do have a reporting
server set up for stuff like this currently, but I donâ't care about these
things taking place on that box.
So the options that I see:
1. Only allow apps to use stored procedures (no dynamic code). This option
just aint gonna happen. Iâ've got over 100 DBâ's already, and I think only 1
adheres to this best practice.
2. Have all apps that want to start using WINNT auth use Application Roles.
This way only the app can connect to the DB, not individual logins. I have no
issue with this, but suspect all the developers that need to re-write their
apps would.
3. Use firewalls to only allow DB connections directly from the app and web
servers. An exception box would probably need to be set up for administrators
to use tools like Enterprise Manager.
These are the options that I see, and was wondering if anyone else has any
other ideas?
TIA, ChrisR.Hi Chris
You should be selective in what users can actually log on to the specific
instances and not allow "Domain Users" this right but a sub group. If a
(different) group of users are allowed to access the database then they
should have the minimum permissions needed to do that so they are forced to
use the application role. If you are using auditing make sure that the
application role is not masking this.
John
"ChrisR" wrote:
> SQL2K now, but will be going to 2005 shortly.
> Howdy all. All of our apps (both inTRAnet and inTERNET) use SQL
> authentication to connect to the DB's. I am well aware of the reasoning to
> use WINNT authentication instead of SQL authentication, but this is currently
> what we do. There are some folks on a mission to get this to change as they
> want "single sign on" for their apps, but I am quite reluctant for the
> following reason:
> We have thousands of employees here. Currently, none of them can connect to
> my OLTP server from outside the app and run their own ad-hoc queries/ DML/
> etc. because their Windows account doesnâ't have permissions to do so and they
> donâ't have the password to the SQL account that the app uses. I ever need to
> worry the end user that decides to use MS Access to query huge amounts of
> data to his remote location and causes blocking without realizing it. Nor do
> I ever need to worry about the developer who likes to run his tests in Prod,
> pegging the CPU, because his test environment doesnâ't contain all the data he
> needs. These are scenarios Iâ've run into in other organizations. Like it or
> not, SQL Authentication does provide these benefits. I do have a reporting
> server set up for stuff like this currently, but I donâ't care about these
> things taking place on that box.
> So the options that I see:
> 1. Only allow apps to use stored procedures (no dynamic code). This option
> just aint gonna happen. Iâ've got over 100 DBâ's already, and I think only 1
> adheres to this best practice.
> 2. Have all apps that want to start using WINNT auth use Application Roles.
> This way only the app can connect to the DB, not individual logins. I have no
> issue with this, but suspect all the developers that need to re-write their
> apps would.
> 3. Use firewalls to only allow DB connections directly from the app and web
> servers. An exception box would probably need to be set up for administrators
> to use tools like Enterprise Manager.
> These are the options that I see, and was wondering if anyone else has any
> other ideas?
> TIA, ChrisR.
>
>

other computer can't connect to my sql server 2000

some of my colleagues need to connect to my database to retrieve some report
s
but they can't connect to my computer. i didn't set any specific passwords a
s
i'm using my window account ( username - Administrator , password - ** )
during the nistallation but the system always show "Connection to Database
Server Fail! Login Failed For User 'sa'" error message whenever they try to
connect to my database. Although i changed the user id in the .ini file to
Administrator also the same. Surely i did wrong somewhere. Please help guys.
Thanks a lot.What version of SQL Server?
Is SQL Authentication enabled?
Why are others attempting to connect using the 'sa' account? (Very BAD
idea!)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Andres" <Andres@.discussions.microsoft.com> wrote in message
news:2090B026-13E1-43E8-BB84-FF34D1AB8840@.microsoft.com...
> some of my colleagues need to connect to my database to retrieve some
> reports
> but they can't connect to my computer. i didn't set any specific passwords
> as
> i'm using my window account ( username - Administrator , password - ** )
> during the nistallation but the system always show "Connection to Database
> Server Fail! Login Failed For User 'sa'" error message whenever they try
> to
> connect to my database. Although i changed the user id in the .ini file to
> Administrator also the same. Surely i did wrong somewhere. Please help
> guys.
> Thanks a lot.

OSQL Utility to connect to the SQL Server.

Hi
Can I just use OSQL in one of my PC which does't have SQL
client to connect to one of the SQL Server?
i.e. I am trying to connect an SQL Server from one of my
PC. In that PC I don't have SQL Client. I don't want to
install SQL Client also. But it is enough to use the OSQL
utility to connect to the SQL Server.
Any answer?
Thanks in advance
Anand.I guess you can try to copy only OSQL.EXE to that machine. But chances are
that is uses some DLL, but you would notice that... Note that you require an
SQL Server Client license per the license agreement to do this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anand" <gurusanand@.yahoo.com> wrote in message
news:0a3f01c3a9af$d9a1dae0$a301280a@.phx.gbl...
> Hi
> Can I just use OSQL in one of my PC which does't have SQL
> client to connect to one of the SQL Server?
> i.e. I am trying to connect an SQL Server from one of my
> PC. In that PC I don't have SQL Client. I don't want to
> install SQL Client also. But it is enough to use the OSQL
> utility to connect to the SQL Server.
> Any answer?
> Thanks in advance
> Anand.|||Tibor Karaszi wrote:
> I guess you can try to copy only OSQL.EXE to that machine. But
> chances are that is uses some DLL, but you would notice that... Note
> that you require an SQL Server Client license per the license
> agreement to do this.
Hi guys.
Anand, I believe the following are needed to run osql.exe (although I
havent tested it yet). Obviously from this list you will notice that
most are either transport files (part of the OS) and OS specific files.
They will obviously need to be registered with the OS.
On a fresh system you will probably only need to install the latest
MDAC and then you should be able to run osql, although not sure the
sqlsrv32.dll is installed by MDAC or through SQL Client install so if
the latter is the case you will need to copy and register this.
Please take note of tibor's comments that a client seat license will
probably still be needed.
osql.exe
C:\WINNT\system32\...
odbc32.dll
odbcint.dll
sqlsrv32.dll
NETAPI32.DLL
secur32.dll
netrap.dll
samlib.dll
ws2_32.dll
ws2help.dll
dnsapi.dll
wsock32.dll
sqlunirl.dll
WINSPOOL.DRV
OLE32.DLL
NDDEAPI.DLL
sqlsrv32.rll
odbccp32.dll
dbnetlib.dll
security.dll
msapsspc.dll
SCHANNEL.DLL
msnsspc.dll
digest.dll
MSV1_0.DLL
ntdsapi.dll
msafd.dll
wshtcpip.dll
RNR20.DLL
IPHLPAPI.DLL
icmp.dll
mprapi.dll
activeds.dll
adsldpc.dll
rtutils.dll
SETUPAPI.DLL
USERENV.DLL
RASAPI32.DLL
RASMAN.DLL
TAPI32.DLL
DHCPCSVC.DLL
software.LOG
rtutils.dll
winrnr.dll
rasadhlp.dll
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

osql to connect to a Database

I am running MSDE 2000 Rel A on a Windows XP machine and am able to
connect to it by doing the following:
osql -U sa
or
osql -E
But how do I connect to a Particular Database or see all the tables
etc? I am a but familiar with sql (which could be the problem) and an
trying to do the following that I can with it:
sql user_name/database_name@.password
so that I can connect directly to the DB I'm interested and then I can
do:
select tablenames;
desc table_name;
select * from table_name;
etc
Any ideas? Like I mentioned, maybe I'm trying to do things that is not
supposed to be possible with osql (which would really suck).
- manzoorosql -S (local) -E -d pubs
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105052724.438484.94120@.z14g2000cwz.googlegroups.com...
>I am running MSDE 2000 Rel A on a Windows XP machine and am able to
> connect to it by doing the following:
> osql -U sa
> or
> osql -E
> But how do I connect to a Particular Database or see all the tables
> etc? I am a but familiar with sql (which could be the problem) and an
> trying to do the following that I can with it:
> sql user_name/database_name@.password
> so that I can connect directly to the DB I'm interested and then I can
> do:
> select tablenames;
> desc table_name;
> select * from table_name;
> etc
> Any ideas? Like I mentioned, maybe I'm trying to do things that is not
> supposed to be possible with osql (which would really suck).
> - manzoor
>|||Hi Hassan,
From your posting it looks like 'sa' password is blank (that is incase you
are not giving any password when OSQL utility prompts).
Best practice is to change the 'sa' pwd to something else, as it gives
access to anybody in your network, with sql client.
If you are not using blank pwd, please ignore this message.
--
Thanks
Yogish|||thanx a million!!!!
- manzoor

osql to connect to a Database

I am running MSDE 2000 Rel A on a Windows XP machine and am able to
connect to it by doing the following:
osql -U sa
or
osql -E
But how do I connect to a Particular Database or see all the tables
etc? I am a but familiar with sql (which could be the problem) and an
trying to do the following that I can with it:
sql user_name/database_name@.password
so that I can connect directly to the DB I'm interested and then I can
do:
select tablenames;
desc table_name;
select * from table_name;
etc
Any ideas? Like I mentioned, maybe I'm trying to do things that is not
supposed to be possible with osql (which would really suck).
- manzoor
osql -S (local) -E -d pubs
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105052724.438484.94120@.z14g2000cwz.googlegro ups.com...
>I am running MSDE 2000 Rel A on a Windows XP machine and am able to
> connect to it by doing the following:
> osql -U sa
> or
> osql -E
> But how do I connect to a Particular Database or see all the tables
> etc? I am a but familiar with sql (which could be the problem) and an
> trying to do the following that I can with it:
> sql user_name/database_name@.password
> so that I can connect directly to the DB I'm interested and then I can
> do:
> select tablenames;
> desc table_name;
> select * from table_name;
> etc
> Any ideas? Like I mentioned, maybe I'm trying to do things that is not
> supposed to be possible with osql (which would really suck).
> - manzoor
>
|||Hi Hassan,
From your posting it looks like 'sa' password is blank (that is incase you
are not giving any password when OSQL utility prompts).
Best practice is to change the 'sa' pwd to something else, as it gives
access to anybody in your network, with sql client.
If you are not using blank pwd, please ignore this message.
Thanks
Yogish
|||thanx a million!!!!
- manzoor

osql to connect to a Database

I am running MSDE 2000 Rel A on a Windows XP machine and am able to
connect to it by doing the following:
osql -U sa
or
osql -E
But how do I connect to a Particular Database or see all the tables
etc? I am a but familiar with sql (which could be the problem) and an
trying to do the following that I can with it:
sql user_name/database_name@.password
so that I can connect directly to the DB I'm interested and then I can
do:
select tablenames;
desc table_name;
select * from table_name;
etc
Any ideas? Like I mentioned, maybe I'm trying to do things that is not
supposed to be possible with osql (which would really suck).
- manzoorosql -S (local) -E -d pubs
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105052724.438484.94120@.z14g2000cwz.googlegroups.com...
>I am running MSDE 2000 Rel A on a Windows XP machine and am able to
> connect to it by doing the following:
> osql -U sa
> or
> osql -E
> But how do I connect to a Particular Database or see all the tables
> etc? I am a but familiar with sql (which could be the problem) and an
> trying to do the following that I can with it:
> sql user_name/database_name@.password
> so that I can connect directly to the DB I'm interested and then I can
> do:
> select tablenames;
> desc table_name;
> select * from table_name;
> etc
> Any ideas? Like I mentioned, maybe I'm trying to do things that is not
> supposed to be possible with osql (which would really suck).
> - manzoor
>|||Hi Hassan,
From your posting it looks like 'sa' password is blank (that is incase you
are not giving any password when OSQL utility prompts).
Best practice is to change the 'sa' pwd to something else, as it gives
access to anybody in your network, with sql client.
If you are not using blank pwd, please ignore this message.
Thanks
Yogish|||thanx a million!!!!
- manzoor

OSQL question

From a command prompt I try to connect to my newly installed isntance of
MSDE. I use the following:
osql -U sa
and I get:
[SHARED MEMORY] Invalid Connection
[SHARED MEMORY] ConnectionOpen(InvalidInstance())
What am I doing wrong here?
Thanks for the information.
Brad
Did you install a named instance?
Specify the server name on the osql command line.
Also, I hope you didn't setup your MSDE instance with a blank password.
Jim
"Brad" <allcomppc@.sbcglobal.net> wrote in message
news:OsYsz1dyEHA.1204@.TK2MSFTNGP10.phx.gbl...
> From a command prompt I try to connect to my newly installed isntance of
> MSDE. I use the following:
> osql -U sa
> and I get:
> [SHARED MEMORY] Invalid Connection
> [SHARED MEMORY] ConnectionOpen(InvalidInstance())
> What am I doing wrong here?
> Thanks for the information.
> Brad
>
|||I did install a named instance with also setting the sa password.
I tried using the -S <server> but I get the same message.
Brad
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
> Did you install a named instance?
> Specify the server name on the osql command line.
> Also, I hope you didn't setup your MSDE instance with a blank password.
> Jim
> "Brad" <allcomppc@.sbcglobal.net> wrote in message
> news:OsYsz1dyEHA.1204@.TK2MSFTNGP10.phx.gbl...
>
|||try OSQL.EXE -L
[vbcol=seagreen]
>I did install a named instance with also setting the sa password.
> I tried using the -S <server> but I get the same message.
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
|||Try -S <server>\<Instance name>
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Brad" <allcomppc@.sbcglobal.net> wrote in message
news:%23PKaFvmyEHA.2316@.TK2MSFTNGP15.phx.gbl...
>I did install a named instance with also setting the sa password.
> I tried using the -S <server> but I get the same message.
> Brad
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> news:%23nHX%23igyEHA.1392@.tk2msftngp13.phx.gbl...
>

osql

i'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>osql -S (local)\\SQLEXPRESS -U sa -P sablah -i run.sql.......

this is the error i'm getting in my logs

Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

First, you only use EITHER username/password OR Trusted_Connection -NOT both. So the second connection string should fail.

Second, in the connection string, the server 'should' have only one [\] between the machinename and the instancename.

|||Yah, i believe i posted that. for asp.net the string actually works. i know why its not supposed to be there. and thats obivously why i can't add that argument to the osql statement. but i'm still left with the original posting of my error. i'm not missing anything in my osql statement?|||

Reading your post again, it seems that you indicated that your ASP connection string failed.

is98 wrote:

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

ASP may be able to handle the two backslashes in the servername, but OSQL cannot. And if I recall, doesn't ASP use (localhost) instead of (local)?

Try: "server=(local)\SQLExpress" INSTEAD OF "Server=(local)\\SQLEXPRESS"

|||who the man? you the man. the localhost part works still ok. i just took out the extra slash which obiviously now explains why it couldn't find the server. thanks muchly.|||

The double back-slash is an escape character for C based languages, such as C#. When ever you want to pass a backslash in a C based language, you either need to use the "\\" escape sequence or use the @. to indicate a litteral string. You are passing literal strings to oSQL, not C#, so the "\\" is not needed, as you've found.

Just thought you'd be interested in understanding that this wasn't just random behaivor.

Mike