question when using application role

Hi,
If i use application role...how do i pass the connection string in order
to connect to sql server? Can anyone give me an example?
Thanks a lot!
regards,
florenceleeWhen you use an application role, the app connects to the server in EXACTLY
the same way it would if there were NO appl role..
However AFTER the connection is made the app executes.
sp_Setapprole stored procedure, passing in the role name and password.
There is an example in books online. This causes the entire permission set
for the connection to be completely replaced by the permissions associated
with the role...
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:etEWinVzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If i use application role...how do i pass the connection string in order
> to connect to sql server? Can anyone give me an example?
> --
> Thanks a lot!
> regards,
> florencelee
>

question when using application role

Hi,
If i use application role...how do i pass the connection string in order
to connect to sql server? Can anyone give me an example?
--
Thanks a lot!
regards,
florenceleeWhen you use an application role, the app connects to the server in EXACTLY
the same way it would if there were NO appl role..
However AFTER the connection is made the app executes.
sp_Setapprole stored procedure, passing in the role name and password.
There is an example in books online. This causes the entire permission set
for the connection to be completely replaced by the permissions associated
with the role...
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:etEWinVzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If i use application role...how do i pass the connection string in order
> to connect to sql server? Can anyone give me an example?
> --
> Thanks a lot!
> regards,
> florencelee
>

question when using application role

Hi,
If i use application role...how do i pass the connection string in order
to connect to sql server? Can anyone give me an example?
Thanks a lot!
regards,
florencelee
When you use an application role, the app connects to the server in EXACTLY
the same way it would if there were NO appl role..
However AFTER the connection is made the app executes.
sp_Setapprole stored procedure, passing in the role name and password.
There is an example in books online. This causes the entire permission set
for the connection to be completely replaced by the permissions associated
with the role...
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:etEWinVzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If i use application role...how do i pass the connection string in order
> to connect to sql server? Can anyone give me an example?
> --
> Thanks a lot!
> regards,
> florencelee
>

Question regarding SQL Express and Connection Strings

Hello everyone!

I am having some more problems while trying to get my site online. I built the site offline using Visual Web Developer Express Edition, and used "SQL Server Express" that comes with ASP.Net. Therefore, I have the ASPNETDB.mdf and mydatabase.mdf in the App_Data folder. Now, on my local machine everything runs fine. When I tried to put the site online however, I get the following error:

An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26- Error Locating Server/Instance Specified)

This is the connection string in my web.config file:

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Now, my web-host does have SQL Server 2005. Does my ASP.Net application use that though, since I have it running just through SQL EXPRESS? Also, does anyone know where I could change my "SQL Server 2005" settings? My web-host uses Plesk 8.2.0, and the database managing program is "ASP.Net Enterprise Manager", but I can't find any options anywhere to change the Server settings to allow remote connections.

Any help would be greatly appreciated! Thanks in advance,

Markuu

You will need to upsize from the sql 2005 express to sql 2005 databases or database.

A good guide to the steps needed is at

http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

Hope it helps

|||

I don't believe Remote Connections is the issue. The error message clearly states that it could not find the instance of Sql Server that is specified in Data Source part of your connection string - (local)\SQLEXPRESS. That part is the address and name of the Sql Server you are trying to attach to. It's highly improbable that the instance you are using in a hosted environment is called SQLEXPRESS (which is the default name given to instances of Sql Server Express).

I'm not familiar with Plesk or your management system, but usually Web hosting companies will give you an instance that uses something like your user name, or account name. They should also provide a sample connection string and some guidance in a FAQ somewhere. If you can't find it, contact their support.

General connection string related stuff can be found here: www.connectionstrings.com

Question on VBScript connection issue and SQL 2005

I have a script that performs a number of reiterative tasks very quickly on a SQL database. After a few thousand requests (2-3 minutes) I get the following error:

Error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Code: 80004005
Source: Microsoft OLE DB Provider for ODBC Drivers

The connection string looks like:

Driver={SQL Server};Server=45.22.11.33;Uid=test;Pwd=test;Database=Test

I have changed nothing and it always does this. It doesn't matter if in my script I open and close the connection on every request or leave it open the whole time.

Additionally, the script and the database are on the same machine.

Thanks for any help.There might be various reasons for this. How many concurrent user connections are made in your case? Are you sure you're not exceeding the limit per your license?|||

Is it possible that the IP address on the target machine was changed for some reasons? Can you try the following things?

1) ping 45.22.11.33.

2) telnet 45.22.11.33 1433

If any of this failed, you may have some network issue. Thanks.

question on tasks

hi,

if a package fails on error, would i be able to know code wise which particular task failed?
also, if the task is using a connection manager, how will i be able to access that particular connection object?

If you turn on logging then you will know what failed. Does that not suffice?

-Jamie

|||

i'm not sure if this answers you questions, however...

you could execute the package programatically and trap for a DtsTaskException that may be generated. then, you can determine which task caused the error by checking the subcomponent property of the dtserror object.

i believe that you should already know which connection manager a task uses because (as far as i know) this property cannot be set dynamically.

|||

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

|||

Ranier wrote:

if a task fails, i want to be able to do this PROGRAMMATICALLY on error event:

1. know which task failed and get the connection object used by the task, if any.
2. so that i will try to establish connection to the connection object and
3. reexecute the task if #2 is successful.

please let me know how to do this.

thanks.

do you really want to do this? why don't you just execute the package within a transaction and then re-execute it if the package fails?|||

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

|||

Ranier wrote:

Hi Duane,

In essence, I want to do a retry component for every task... is this possible?

it may be possible, but unnecessary.

if i understand you correctly, you want to re-execute a task if it fails. why do you want to do this? wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it?

|||

the component that i want to make should be on a script task. i need to be able to reuse it on every package just drag and drop.

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

|||

Ranier wrote:

why do i need to retry? in cases such as intermittent connection or the target server suddenly reboots and becomes back online after a couple of minutes, i would want the script task to retry the task that failed programmatically and without human intervention.

wouldn't it make more sense to rollback the entire package if a task fails, and then re-execute it? you can use transactions and checkpoints to accomplish this.

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Question on connection to SQL Server using windows authentication

I have a domain user account through which I can log on a machine. The machine allows me to use this domain user account to create DB in SQL Server using windows authentication. How can I define a connection string to connect SQL Server? My connection string is like:
string str = "server=(local);uid='"+userName+"';password='"+password+"';database='master';connection timeout=15";

This one works on another machine where I use SA account. But this one does not work using windows authentication. I tried to use domainname\myname as user name, but failed.
Could any one give me some suggestion?I solved the problem.|||Hi bagofbones.

I have the same problem.

Please post your solution.

Thanks.

DS

Question on connecting to Oracle

Hello all,

I'm using a OLE DB Connection Manager to Oracle (I've installed the client) and using Oracle Native OLE DB Provider.

The problem was the view In Oracle I'm returning a column that uses a fuction on a numeric field in the the select part of the statement, and by this, the Precition is not displayed when I'm trying to look at the data types of the fields in the view.

The Precision is shown as 0 in the external field part of the component.

I have read the previous post concerning the problem connecting to Oracle Using the Oracle provider, and solved it by using the Microsoft provider, but still I have a question about the oracle provider:

I changed the precision on the External column (from 0 to 15) and also an the Output column, but now I'm getting an error on about error output not matching:

Error 6 Validation error. Data Flow Task: OLE DB Source 1 [8970]: The output column "UPDATE_TIME_NUM" (9333) on the non-error output has no corresponding output column on the error output. Dim_registered_user.dtsx 0 0

I cannot change the error output datatype, cannot delete it or do anything at all.

Is there a way to fix the problem?

This may or may not help you, but I've switch my Oracle connections over to the ADO.NET provider for Oracle. The only side effect is that the resulting data flow is Unicode by default...so all your string data is DT_WSTR instead of DT_STR. You can change that, but it's a manual operation go through all the External Columns and change their type.

I had some weird data issues like you describe above and after poking around the internet came across this article by Donald Farmer (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx). That article explains alot about SSIS Oracle issues.

|||The problem with ADO.NET provider is that it doesn't allow the use of variables as a source SQL

question on AqcuireConnection

Hi,

I created a function that supposedly checks a connection manager if a connection can be established.
When I was testing the code, I found out that the following code always returns "Successful" for flat files and SMTP connection even if the flat file does not even exist or there are no connection parameters for SMTP.

Can you tell me what to use to check if a connection can be established for all types of connection managers?

Here is the function that I created which does not seem to work as I expected:

Public Function connect(ByVal connMgr As Object) As String
Dim connected As String
Try
Dts.Connections(connMgr.name).AcquireConnection(Nothing)
Return "Successful"
Catch ex As Exception
Return "Failure"
Finally
Dts.Connections(connMgr.name).ReleaseConnection(Nothing)
End Try
End Function

Thanks.

AcquireConnection returns an object that represents the connection. You need to check this object. It will be different for each connection manager.

An ADO.NET connection manager will return an IDbConnection object.

The SMTP Connection manager simply returns the connection string of the connection manager.

There isn't a generic way to determine if a connection can be established for all types of connection managers.
|||

A flat file does not need to exist for AcquireConnection to be successful. It can point to a destination file to be created at the runtime.

Thanks.