DSN or DSNless connection?

I'm starting to move my VB6 application from Access to SQL Server. In the
beginning I'm going to use Access database with tables linked to SQL Server
tables. Then form by form are going to be moved to direct SQL Server
connection. When all of them are moved I delete Access. I have very weak
experience with SQL Server, so I even do not know advantages and
disadvantages of DSN and DSN less connections. I know that I have to learn
everything and I'm going to do that. But for the beginning could somebody
give me some suggestions regarding connection. What is better ? DSN or DSN
less?
Thank you
Vladhttp://groups.google.com/groups?q=dsn+vs+dsn-less
-oj
http://www.rac4sql.net
"Vlad" <vovan.c@.verizon.net> wrote in message
news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
> I'm starting to move my VB6 application from Access to SQL Server. In the
> beginning I'm going to use Access database with tables linked to SQL
Server
> tables. Then form by form are going to be moved to direct SQL Server
> connection. When all of them are moved I delete Access. I have very weak
> experience with SQL Server, so I even do not know advantages and
> disadvantages of DSN and DSN less connections. I know that I have to learn
> everything and I'm going to do that. But for the beginning could somebody
> give me some suggestions regarding connection. What is better ? DSN or DSN
> less?
> Thank you
> Vlad
>|||Thank you very much
Vlad
"oj" <nospam_ojngo@.home.com> wrote in message
news:OYlwjdlAEHA.2448@.TK2MSFTNGP12.phx.gbl...
> http://groups.google.com/groups?q=dsn+vs+dsn-less
> --
> -oj
> http://www.rac4sql.net
>
> "Vlad" <vovan.c@.verizon.net> wrote in message
> news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
the
> Server
learn
somebody
DSN
>

DSN less connection fro ASP pages

I have installed Microsoft SQL server 2000 on a server.
My web server is having windows NT having all asp pages.
I want to connect to SQL Server 2000 using a DSN less
connection for my asp pages. Could anyone help me with
that?
Also, if I try to create system DSN on windows NT, it
gives me an error message and does not create DSN. What
could be the reason?
ThanksThe syntax depends on whether you are using OLE DB or ODBC,
it you are connecting using a standard SQL login or Windows
Authentication, etc. You can find sample ADO connection
strings which cover different scenarios at:
http://www.able-consulting.com/ADO_Conn.htm
The problem creating the DSN would depend on what the error
message is that you are receiving - it's hard to say without
more information about the error.
-Sue
On Wed, 25 Feb 2004 13:43:59 -0800, "namita"
<anonymous@.discussions.microsoft.com> wrote:

>I have installed Microsoft SQL server 2000 on a server.
>My web server is having windows NT having all asp pages.
>I want to connect to SQL Server 2000 using a DSN less
>connection for my asp pages. Could anyone help me with
>that?
>Also, if I try to create system DSN on windows NT, it
>gives me an error message and does not create DSN. What
>could be the reason?
>Thanks|||Hello,
<%
set conn1=server.CreateObject("adodb.connection")
conn1.cursorlocation=3
conn1.Open "Provider=sqloledb;" & _
"Data Source=Server;" & _
"Initial Catalog=DB;" & _
"User Id=ayaz;" & _
"Password=ayaz"
%>
and also
try this one too.
conn1.open " dsn=ayaz;uid=ayaz;pwd=ayaz;DATABASE=ayaz
;APP=ASP Script"
i hope its will help you , if any problem then email me.
Thanks,
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

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

DSN Connection to SQL Server

I am using the code below to create a DSN connection if it does not exist. Is there a way to set the DSN to use NT Authentication?

lngResult = SQLConfigDataSource(0, _
ODBC_ADD_SYS_DSN, _
"SQL Server", _
"DSN=" & JDS_DSN_name & Chr(0) & _
"Server=" & JDS_Server_name & Chr(0) & _
"Database=RMAData" & Chr(0) & _
"UseProcForPrepare=Yes" & Chr(0) & _
"Description=RMA Database" & Chr(0) & Chr(0))Figured it out|||Try specifying "Trusted_Connection=No" attribute along with the other attributes specified in SQLConfigDataSource().

DSN Connection to Remote SQL Server

Hi,

I've worked with SQL Server plenty but never in this specific manner. Hoping someone can shed some light.

I have a webform on web server A that needs to talk to a SQL server database that web server B accesses via its web application. I do not have
any privelidged access to server B or the sql server it uses (no FTP to webserver, no remote access via Enterprise Manager to the SQL server). The Admin of Server B told me that I can use a DSN connection to talk to the SQL server database that the web app on server B uses. He emailed me the names of the SQL database tables AND a copy of the web.config from server B which shows the DSN implemented like this:

<appSettings>
<add key="dsn" value="data source=sql3.domainname.net;database=dbname;User Id=dbuserid;password=dbpw;" />
<add key="default_language" value="en" />
<add key="error_email_to" value="someone@.domainname.com" />
<add key="baseHref" value="http://www.mysite.com/" />
<add key="website_name" value="$$$$$$$$$$$$$$$$$" />
</appSettings>

I was planning to run my webforms on Server A where I do have FTP, Control Panel, etc.
So, I wrote this code into my VB.NET codebehind file.

Dim oODBCConnectionAs OdbcConnection
Dim sConnStringAsString = _
"Driver={SQL Server};" & _
"Server=sql3.domainname.net;" & _
"Database=dbname;" & _
"Uid=dbuserid;" & _
"Pwd=dbpw"

oODBCConnection =New Odbc.OdbcConnection(sConnString)
oODBCConnection.Open()
... commands to access data and load something into a control on a webform
oODBCConnection.Close()

When I attempt to run the webform in my browser from Server A. I'm getting this error:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
The error is EXACTLY the same whether I place the appSettings section in server A's web.config or leave them out.

I have obvious questions about the error like...
Might sql3.domainname.net simply be refusing communication requests that don't come from the Server B domain?
Might the sql3.domainname.net be setup so that I need to use another protocol (not tcp/ip)?
Might my code need to be changed to support this type of connection (i.e. I didn't code it right)?

I spent some time readinghttp://support.microsoft.com/kb/328306/en-us but I'm at a loss as to what the best course is to take for debugging this.
Thanks in advance for any insight you can offer.

Stewart

Hi,

I think you should get the remote server's ip address and user account to creat a local DSN and call the DSN in your application. Another easy solution is to use SqlConnection rather then ODBCConnection and modify your connection string and make it look like "DataSource=IP address;Initial Catelog=DBName;User Id=;Password="

Thanks.

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

drop table not supported

I create an Execute SQL Task and add a connection and the sql
DROP TABLE Table1
Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
construt or statement is not supported."
Baffling ...John,
I think you are asking about SSIS related queries, and this group is for
Reporting Services.
Amarnath, MCTS
"John Grandy" wrote:
> I create an Execute SQL Task and add a connection and the sql
> DROP TABLE Table1
> Parse Query succeeds. But Build Query gives the error "The DROP TABLE SQL
> construt or statement is not supported."
> Baffling ...
>
>

drop subscription

is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)You can certainly do this using the replication stored procedures, but the GUI can't do it for you. You'll need to get someone that understands both SQL Server replication and your environment in order to do this safely. Microsoft Professional Support Services will help you work through this for a fee.

Another alternative is to backup the database, drop the database, then restore the backup. This is simple and except for the down time relatively painless.

-PatP|||actually the backup, drop and restore solution is the easiest way but you can do that in a test environment but I have a live environment of 50 subscribers and if I want to create a snapshots for these subscribers it'll take a lot of time thats for coping a bout 800MB at least throw a WAN network
However, you can drop the publications and rebuild them on the publisher database.
You cay find the sp_dropsubscriptions but it's not working good
I'm trying to reach someone can drop the subscription for one subscriber only without dropping all publication|||I can do it, and I'm sure that some of the folks here on DBForums can do it, but I don't know any simple way to describe how to do it. You really need to understand how SQL Replication works, and how your particular setup is configured.

I'm sorry, but I don't know of a simple solution to your problem.

The cheapest answer long term is likely to be to call MS-PSS and ask them to walk you through the process. It will cost you some money, but only a tiny fraction of what rebuilding whole databases might cost.

-PatP