Connect to remote instance of SSIS from SSMS using SQL Login?

I have installed a SQL Server 2005 Standard Edition instance with SSIS on Window Server 2003 x64. From my PC, I am able to connect to the Integration Services on the server using Windows Authentication but not with a local SQL login - and the option to change authentication methods is greyed out.

Is it possible to remotely connect to SSIS using a SQL login rather than a Windows login? If so, any ideas or references for configuring it as such?

Thanks.

- Lance

Per the post linked below, it's not supported.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=316143&SiteID=1

connect database user to security login user

After restore off a database the user under security logins was not created.
The user under Database and users was created, but that user MUST bee the
same user under security logins, if i try to make a new user with same name
under logins, thats ok, but i cant connect to the database becuse the user
allready exists, i cant delete the user under the database. What to do? Is
there som way to connect a database user to a security login user. Its SQL
security and login.
/Per W.sp_change_users_login.
Also, you can transport the logins with the correct sid, search KB for sp_he
lp_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Per W." <pwbuf@.tiscali.no> wrote in message news:GdMOd.98909$Vf.3877273@.news000.worldonlin
e.dk...
> After restore off a database the user under security logins was not create
d. The user under
> Database and users was created, but that user MUST bee the same user under
security logins, if i
> try to make a new user with same name under logins, thats ok, but i cant c
onnect to the database
> becuse the user allready exists, i cant delete the user under the database
. What to do? Is there
> som way to connect a database user to a security login user. Its SQL secur
ity and login.
>
> /Per W.
>|||The following will list all users whose SPID is not mapped to a valid login:
sp_change_users_login 'Report'
"Per W." <pwbuf@.tiscali.no> wrote in message
news:GdMOd.98909$Vf.3877273@.news000.worldonline.dk...
> After restore off a database the user under security logins was not
created.
> The user under Database and users was created, but that user MUST bee the
> same user under security logins, if i try to make a new user with same
name
> under logins, thats ok, but i cant connect to the database becuse the user
> allready exists, i cant delete the user under the database. What to do? Is
> there som way to connect a database user to a security login user. Its SQL
> security and login.
>
> /Per W.
>

Confused...SA Account

Hi
I have a two node cluster with SQL2000 SP3. When the node1 fails over to
node2, the SQL Server Agent fails. The log file says "Login Failed for user
'sa'" I don't understand how the SA account can fail on node2 and not on
node1. Can anyone shed some light on this and possibly how to resolve the
issue.
Thank You
Marc
What security account context is the SQL Agent running under? Is it a domain account and does it have adequate permissions/rights on both nodes?
|||Hi Stephen
SQL security is set for "SQL Server and Windows" and the service account is
in the Domain Admins group. Same service account is used for both nodes.
Regards
Marc
"Stephen Strong" <anonymous@.discussions.microsoft.com> wrote in message
news:39A963C4-A818-487F-8B10-C38CC58715FA@.microsoft.com...
> What security account context is the SQL Agent running under? Is it a
domain account and does it have adequate permissions/rights on both nodes?
|||Is the SQL Server Agent startup account a member of the local admin group
on both nodes. If you are using SQL Server authentication for SQL Server
Agent to connect to SQL Server then the SQL Server Agent startup account
has to be a local Admin.
You can check the SQL Agent connection properties by right clicking on SQL
Server Agent - Properties - Connection and see how the connection to SQL
Server is set up.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Thanks Guys
As it turns out, one of our programmers changed the authentication method to
Windows rather than SQL. I changed it back and all is well.
Regards
Marc
"Marc" <Marc@.nospam.com> wrote in message
news:%23u4AZPJJEHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a two node cluster with SQL2000 SP3. When the node1 fails over to
> node2, the SQL Server Agent fails. The log file says "Login Failed for
user
> 'sa'" I don't understand how the SA account can fail on node2 and not on
> node1. Can anyone shed some light on this and possibly how to resolve the
> issue.
> Thank You
> Marc
>

Confirm Password window being displayed

Hi,
When I modifiy the properties of a SQL Server Login, I get the "Confirm
Password" screen displayed. This is displayed even if no change is made to
the login. Is this a bug and is there a hot fix out there.
Just want to know if anyone else has had this problem.
Thank You for your help,
GaryI wanted to add... If I retype the Password on the General Tab of the
Properties screen and make any changes, the Confirm Password screen still
pops up, but it will acknowledge the "new" (really the old one) password whe
n
confirmed and accept my changes. Without doing this, you cannot make any
login changes.
I am on version 2000 sp3 hot fix 818.
This is the only work around I have found so far.
Thanks,
Gary
"Gary Stewart" wrote:

> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made t
o
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary|||Search KB. I'm pretty certain that there's an article on this one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made t
o
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary|||Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group...0f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:

> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
>
>|||This is the tool fix for MS03-031 (Build 818). The tool hotfix is build 819
and there is a link to it right off of the MS03-031 site.
Sincerely,
Anthony Thomas
"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:0BD39043-BF96-41DE-B054-F8A9AAB799E2@.microsoft.com...
Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group...0f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:

> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
to[vbcol=seagreen]
>
>

Confirm Password window being displayed

Hi,
When I modifiy the properties of a SQL Server Login, I get the "Confirm
Password" screen displayed. This is displayed even if no change is made to
the login. Is this a bug and is there a hot fix out there.
Just want to know if anyone else has had this problem.
Thank You for your help,
Gary
I wanted to add... If I retype the Password on the General Tab of the
Properties screen and make any changes, the Confirm Password screen still
pops up, but it will acknowledge the "new" (really the old one) password when
confirmed and accept my changes. Without doing this, you cannot make any
login changes.
I am on version 2000 sp3 hot fix 818.
This is the only work around I have found so far.
Thanks,
Gary
"Gary Stewart" wrote:

> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made to
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary
|||Search KB. I'm pretty certain that there's an article on this one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made to
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary
|||Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group/...f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:

> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
>
>
|||This is the tool fix for MS03-031 (Build 818). The tool hotfix is build 819
and there is a link to it right off of the MS03-031 site.
Sincerely,
Anthony Thomas

"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:0BD39043-BF96-41DE-B054-F8A9AAB799E2@.microsoft.com...
Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group/...f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:
[vbcol=seagreen]
> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
to
>
>

Confirm Password window being displayed

Hi,
When I modifiy the properties of a SQL Server Login, I get the "Confirm
Password" screen displayed. This is displayed even if no change is made to
the login. Is this a bug and is there a hot fix out there.
Just want to know if anyone else has had this problem.
--
Thank You for your help,
GaryI wanted to add... If I retype the Password on the General Tab of the
Properties screen and make any changes, the Confirm Password screen still
pops up, but it will acknowledge the "new" (really the old one) password when
confirmed and accept my changes. Without doing this, you cannot make any
login changes.
I am on version 2000 sp3 hot fix 818.
This is the only work around I have found so far.
Thanks,
Gary
"Gary Stewart" wrote:
> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made to
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary|||Search KB. I'm pretty certain that there's an article on this one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
> Hi,
> When I modifiy the properties of a SQL Server Login, I get the "Confirm
> Password" screen displayed. This is displayed even if no change is made to
> the login. Is this a bug and is there a hot fix out there.
> Just want to know if anyone else has had this problem.
> --
> Thank You for your help,
> Gary|||Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group/microsoft.public.sqlserver.security/browse_frm/thread/c730938ac04e37c3/10f0690f173447fb?tvc=1#10f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:
> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
> > Hi,
> >
> > When I modifiy the properties of a SQL Server Login, I get the "Confirm
> > Password" screen displayed. This is displayed even if no change is made to
> > the login. Is this a bug and is there a hot fix out there.
> >
> > Just want to know if anyone else has had this problem.
> >
> > --
> > Thank You for your help,
> >
> > Gary
>
>|||This is the tool fix for MS03-031 (Build 818). The tool hotfix is build 819
and there is a link to it right off of the MS03-031 site.
Sincerely,
Anthony Thomas
"Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
news:0BD39043-BF96-41DE-B054-F8A9AAB799E2@.microsoft.com...
Hi Tibor,
I did find a HotFix on Google Groups.
See Link below for more information. It has been a problem since 2003.
http://groups-beta.google.com/group/microsoft.public.sqlserver.security/browse_frm/thread/c730938ac04e37c3/10f0690f173447fb?tvc=1#10f0690f173447fb
Thank you for your help.
Gary
"Tibor Karaszi" wrote:
> Search KB. I'm pretty certain that there's an article on this one.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gary Stewart" <GaryStewart@.discussions.microsoft.com> wrote in message
> news:15EB36BD-B036-4C4D-96D8-D683EBDA695E@.microsoft.com...
> > Hi,
> >
> > When I modifiy the properties of a SQL Server Login, I get the "Confirm
> > Password" screen displayed. This is displayed even if no change is made
to
> > the login. Is this a bug and is there a hot fix out there.
> >
> > Just want to know if anyone else has had this problem.
> >
> > --
> > Thank You for your help,
> >
> > Gary
>
>

Confirm password

Hi,
I have a login that has access to a certain database, when
i go to the logins session of EM to give the login acces
to another user, i map the user with the login in a new
database an i give him de db_owner role (Dev Host), when i
do the ok or aply, i'm prompt to confirm the password. For
me this is strange '? The login exists, i just want to
give the user access to another database.
Can anyone explain me this
Thanks in advance
Miguel CorreiaMaybe this will help...
http://support.microsoft.com/default.aspx?kbid=826161
Tim
>--Original Message--
>Hi,
>I have a login that has access to a certain database,
when
>i go to the logins session of EM to give the login acces
>to another user, i map the user with the login in a new
>database an i give him de db_owner role (Dev Host), when
i
>do the ok or aply, i'm prompt to confirm the password.
For
>me this is strange '? The login exists, i just want
to
>give the user access to another database.
>Can anyone explain me this
>Thanks in advance
>Miguel Correia
>.
>

Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!
|||Thanks for the post! Saved my life after hours of searching!!!

Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!
|||Thanks for the post! Saved my life after hours of searching!!!

Configuring SQL Express -- getting login failed exception

I have a machine with Windows XP Pro w/SP 2, IIS, SQL Server Express and Visual Studio 2005 installed. When I attempt to open a database from an ASPX page, I'm getting an exception with the message:

"Cannot open database "Tasks" requested by the login. The login failed.
Login failed for user 'MYDEV\ASPNET'."

I've searched the web and found a number of pages that talk about the "Login failed for MACHINE\ASPNET" issue, but I didn't find any with a solution.

For your information (in case it makes any difference), the computer is connected to a domain. And the authentication methods for the virtual directory (in IIS) is configured for "Anonymous access" and "Integrated Windows authentication."

Does anyone know how to configure SQL Server Express (or is it IIS?) so the web page can access the database?

RichardR

Okay, I found a solution.

For anyone else experiencing this problem, here's what I did:

First, I installed the November CTP of SQL Server Management Studio Express.

Then, I added the account MACHINENAME\ASPNET under Security | Logins. I did a right-mouse click on the ASPNET login name and clicked on Properties.

This brought up the Login Properties page for ASPNET. I mapped the ASPNET account to my database selecting the database in the "Default Database" dropdown.

In order for the stored procedures to execute, I also needed to check the db_owner box under the Mappings tab.

Finally, I needed to change the connection string to:

Data Source=MYDEV\SQLEXPRESS;Connect Timeout=30;User Instance=False;initial catalog=Tasks;Integrated Security=SSPI;Persist Security Info=False

Hope this helps anyone else encountering this problem.

Richard

|||Thanks - that was very helpful!!!|||Thanks for the post! Saved my life after hours of searching!!!