DSN Problems

I have a SQL Server 2000 system dsn that uses SQL Server authentication.
The DSN does not retain the password field entry I make. I enter the
password (the sql login is already there), click next, next then ok.
When I reopen the DSN, the password table is blank again. Help
appreciated.

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Frank Py" <fpy@.proactnet.com> wrote in message
news:3fd614b8$0$196$75868355@.news.frii.net...
> I have a SQL Server 2000 system dsn that uses SQL Server authentication.
> The DSN does not retain the password field entry I make. I enter the
> password (the sql login is already there), click next, next then ok.
> When I reopen the DSN, the password table is blank again. Help
> appreciated.
> Thanks,
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Passwords are not stored in DSNs, as it would be very insecure. You will
need to specify the password (and perhaps username - I'm not sure) at
connection time. This is one good argument for using Windows authentication,
if that's possible in your case. If not, you will need to investigate how to
modify your application to request a username/password from the user.

One other possibility would be to use some sort of middle tier, which would
use a single DSN for all connections to SQL Server, but this may not be
practical or desirable, depending on your application setup.

Simon|||Yes, I can use Windows Authentication. The person connecting to the DSN
is an administrator. So if I use Windows Authentication instead on this
DSN, would that be more transparent to the user? The user, who is an
administrator needs to run a Crystal Report that is connected to the DSN
without being prompted for a username and password each time. Help
appreciated. Thanks.

Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Frank Py <fpy@.proactnet.com> wrote in message news:<3fd635b0$0$198$75868355@.news.frii.net>...
> Yes, I can use Windows Authentication. The person connecting to the DSN
> is an administrator. So if I use Windows Authentication instead on this
> DSN, would that be more transparent to the user? The user, who is an
> administrator needs to run a Crystal Report that is connected to the DSN
> without being prompted for a username and password each time. Help
> appreciated. Thanks.
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Yes - using Windows authentication means that no username and password
should be required, in the same way as connecting with Query Analyzer
or OSQL, for example.

Simon

DSN connection w/o password

hi all

i want to connect to SQL Server using DSN.

I create a System DSN, say 'DSN_PWD', provide SQL server authentication in that step and test the connection which works fine.

Now, currently i am using a code by my seniors which is as below

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN_PWD","username","password"
objConn.Close
Set objConn =Nothing

what i am concerned is that this will be saved as ASP file, i don't want any login informatoin to be saved in a physical file openly.

i have tried this but it gives error

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString="DSN=DSN_PWD;"
objConn.Open

objConn.Close
Set objConn =Nothing

Error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user

is there any effective way?

Thanks all

Yogesh JangamYou can hardcode the username and the password within the DSN itself using the ODBC Connection Manager. Is that what you want?

-PatP|||my primary concern being security, i don't want any SQL login information to be saved in any ASP files.

I am creating DSN with SQL Authentication with Login ID/PWD entered by user

i want a method to use this DSN to create an ADODB.Connection w/o manually again typing username and password in objConn.open method

i hope i am clear ;-).......

i have looked for connection code, but most of them have login info in the Open method.

Thanks
Yogesh Jangam|||The easiest way to do this is if your IIS service runs as an NT account. Then just use Windows Authentication for your ODBC connection and you are "good to go" without any fuss at all.

If you must run your IIS Service as LocalSystem, then you need to butcher a DSN to force static SQL authentication. That would be a last resort.

-PatPsql

Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >