sql login permissions: connect

Showing posts with label connect. Show all posts

Wednesday, March 21, 2012

LogReader unable to connect to the publisher

I have a SQL2K box acting as Distributor and SQL7 box as a publisher. The
LogReader on the Distributor is unable to connect to the Publisher. I get the
following Msg from the Log Reader:
The process could not execute 'sp_MSadd_repl_commands27hp' on
'SQL2KServerName\InstanceName'
The two servers are on different untrusted domains. I am however able to
connect both ways through QA.
SQL2K box has SP4 and SQL7 box has SP3. Don't want to go to SP4 on SQL7
unless necessary and because it is a production server.
can you uncheck the Administrative Link Password requirement. Connect to
your distributor in EM, select Tools, Replication, Configure Distributor,
Publishers and Subscribers, and click on the Publishers tab. On the problem
publisher click on the browse button to the right of the publisher and you
will find this option at the bottom of the dialog box.
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:74DD5392-6B69-43B7-A591-4D80901F97D8@.microsoft.com...
>I have a SQL2K box acting as Distributor and SQL7 box as a publisher. The
> LogReader on the Distributor is unable to connect to the Publisher. I get
> the
> following Msg from the Log Reader:
> The process could not execute 'sp_MSadd_repl_commands27hp' on
> 'SQL2KServerName\InstanceName'
> The two servers are on different untrusted domains. I am however able to
> connect both ways through QA.
> SQL2K box has SP4 and SQL7 box has SP3. Don't want to go to SP4 on SQL7
> unless necessary and because it is a production server.
|||Thank you Hiary for the response. It turned out that the Distributor was
unable to resolve Publisher's IP address by name. Somehow, replacing the IP
address in the parameters for the log reader didn't work either. However,
after modifying the host file (for IP to name lookup) for distributor
resolved the problem.
-A
"Hilary Cotter" wrote:

> can you uncheck the Administrative Link Password requirement. Connect to
> your distributor in EM, select Tools, Replication, Configure Distributor,
> Publishers and Subscribers, and click on the Publishers tab. On the problem
> publisher click on the browse button to the right of the publisher and you
> will find this option at the bottom of the dialog box.
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:74DD5392-6B69-43B7-A591-4D80901F97D8@.microsoft.com...
>
>

Logreader down question

If the logreader stops (separate distributor server from publihser) and
cannot connect to the publisher for - let's say - 4 hours, is that data lost
or will the transactions to be replication still be in the tlog? and
replication will catch up - in total (including the 4 hours down)?
Just to clarify...
The Transaction Retention period is the default 72 hours in the Distribution
properties. ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:28c201c51dbe$9111c5e0$a601280a@.phx.gbl...[vbcol=seagreen]
> 4 hours shouldn't be a problem.
> Firstly, the commands won't be removed from the log until
> they have been read by the log reader, so they'll remain
> there.
> The time that this can be an issue is if 4 hours is
> longer than the transaction retention period. If this is
> the case, the distribution cleanup agent can remove
> commands before the distribution agent propagates them,
> in which case you'll end up having to reinitialize to
> recover.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
> publihser) and
> hours, is that data lost
> the tlog? and
> hours down)?
|||Yes - if you have the default of 72 hours, then you're
fine

Rgds,
Paul Ibison
sql

Monday, March 19, 2012

Logon Failure during exporting

Using Crystal Report for VS.Net in one of my .Net project, to connect to Oracle database "myDB" to generate a report. Things seems OK till it hits the "ExportToDisk" method, and gave me a "Logon Failure" exception.

Googled around and somebody said that I have to use "setDatabaseLogon()" method to set the uid/pass during runtime. So I did but it still didn't work.

Here's my source code, hope somebody could shed some light on this. Thanks !

******************************
"myConnection" is an estabilished OleDbConnection that was used repeatedly through the project. "dsTemp" is a dataset
******************************
Dim rptWKIT As New crWKIT()
Dim exportFileName As String = "exportedReport.rpt"
Dim exportPath As String = Application.StartupPath & "\" & exportFileName

Try
dsTemp.Clear()
Dim myDA As New OleDb.OleDbDataAdapter()
Dim myCmd As New OleDb.OleDbCommand()
myCmd.Connection = myConnection
myCmd.CommandText = "SELECT * FROM WKIT"
myCmd.CommandType = CommandType.Text
myDA.SelectCommand() = myCmd
myDA.Fill(dsTemp, "WKIT")

rptWKIT.SetDataSource(dsTemp)
rptWKIT.SetDatabaseLogon(sUser, sPass)
rptWKIT.Load()

rptWKIT.ExportToDisk(ExportFormatType.RichText, exportPath)
Catch x As Exception
MessageBox.Show(x.Message + x.Source + x.StackTrace, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End TrySee if you find asnwer here
http://support.businessobjects.com/|||Thanks. I just figured this out... after the rptWKIT.SetDataSource(dsTemp), there's no need to do the next 2 steps:

rptWKIT.SetDatabaseLogon(sUser, sPass)
rptWKIT.Load()

It can just export without any problem. But when do Load() again, guess CR is confused...|||Glad to know you sorted it out :)

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Hi All,

I am getting the following error in the ERRORLOG File when I am
trying to connect to SQL Server 2005 Enterprise Edition 64bit with SP2 from
Reporting Services Configuration tool - Database Setup. The SQL Server
database and reporting services are on 2 different servers. Both are running
2003 SP2 64 bit.

2007-04-12 07:30:05.54 Logon Error: 18456, Severity: 14, State:
8.
2007-04-12 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]

I know my password is valid as I can logged into SSMS using SA. I can create
the 2 reportserver databases using the Reporting Services Configuration tool
on the database server. The db server is in MIXED mode.

What am I missing?

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Hi All,

2007-04-12 07:30:05.54 Logon Error: 18456, Severity: 14, State:
8.
2007-04-12 07:30:05.54 Logon Login failed for user 'sa'.
[CLIENT: xx.xx.xx.xx]

What am I missing?

Friday, March 9, 2012

loging in to sql express

I am trying to login into sql express using studio express and keep getting this msg.

"Cannot connect

....... under the default setting SQL Server does not allow remote connections. (provider: Named Popes Provider, error: 40 - could not open a connection to sql server) (sql server error2)"

I did try and make a change under surface area configuration to include local and remote connections but it did not help. Any thoughts? Thanks!

David

Are you using the SQL Server Browser service ? If not it could that that you might not use the default port of 1433. If so, type in the port number after the InstanceName using the syntax Servername\InstanceName,PortNumber which should be in your case something more specific Servername\SQLExpress,PortNumber

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Jens, thanks. You gave me the idea to start using the browser service and I logged in that way.

Login without Connect Permission somehow managed to become db_owner!?

I tried asking the same question in other forum but couldn't get an answer.

I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'

Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.

What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!

It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.

also post the reslut of

sp_helplogins 'yy'

sp_helpuser 'yy'

Madhu

|||

You can also use the following two queries to determine the role memberships and the permissions associated with login yy:

select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('yy')
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('yy')

Thanks
Laurentiu

|||

Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.

Madhu,

i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':

LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no

and the 2nd result set is empty.

Laurentiu,

I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.

After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q

|||

Are you connecting to server using this Login from your application ? If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.

Madhu

|||

Check the context you are logged in:

select suser_name()

and also check who owns the database by querying sys.databases:

select name, suser_sname(owner_sid) from sys.databases

Thanks
Laurentiu

|||

Madhu,

Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.

Laurentiu,

sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:

Use ApplicationDB
GO
select suser_name()

Result: yy

And also i run the following:
select name, suser_sname(owner_sid) from sys.databases

Result: indicating my applicationDB is own by domain\Administrator

I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?

|||

OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database?

I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:

select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.

Thanks
Laurentiu

|||

Laurentiu,

Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

Returns me:

permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5

this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.

|||See BOL under of DB Roles

Every database user belongs to the public database role. When

a user has not been granted or denied specific permissions on a securable, the

user inherits the permissions granted to public on that securable.

You must have changed your "public" role on the 2nd database, which the user does not see.

|||

The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?

Thanks
Laurentiu

PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.

|||

Tom,

i have compare my public role between the accessible application db and other none accessible db, i find no difference.

Laurentiu,

if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.

i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q

|||

I'm out of ideas. Here are the two suggestions I have left:

(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.

(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.

Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.

Thanks
Laurentiu

|||

Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:

SELECT * FROM sys.login_token ORDER BY type, usage, name

SELECT * FROM sys.user_token ORDER BY type, usage, name

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Login without Connect Permission somehow managed to become db_owner!?

I tried asking the same question in other forum but couldn't get an answer.

I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'

What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!

also post the reslut of

sp_helplogins 'yy'

sp_helpuser 'yy'

Madhu

|||

You can also use the following two queries to determine the role memberships and the permissions associated with login yy:

Thanks
Laurentiu

|||

Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.

Madhu,

i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':

LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no

and the 2nd result set is empty.

Laurentiu,

I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.

|||

Madhu

|||

Check the context you are logged in:

select suser_name()

and also check who owns the database by querying sys.databases:

select name, suser_sname(owner_sid) from sys.databases

Thanks
Laurentiu

|||

Madhu,

Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.

Laurentiu,

sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:

Use ApplicationDB
GO
select suser_name()

Result: yy

And also i run the following:
select name, suser_sname(owner_sid) from sys.databases

Result: indicating my applicationDB is own by domain\Administrator

I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?

|||

I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:

select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.

Thanks
Laurentiu

|||

Laurentiu,

Returns me:

permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5

this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.

|||See BOL under of DB Roles

Every database user belongs to the public database role. When

a user has not been granted or denied specific permissions on a securable, the

user inherits the permissions granted to public on that securable.

You must have changed your "public" role on the 2nd database, which the user does not see.

|||

The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?

Thanks
Laurentiu

PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.

|||

Tom,

i have compare my public role between the accessible application db and other none accessible db, i find no difference.

Laurentiu,

i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q

|||

I'm out of ideas. Here are the two suggestions I have left:

Thanks
Laurentiu

|||

Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:

SELECT * FROM sys.login_token ORDER BY type, usage, name

SELECT * FROM sys.user_token ORDER BY type, usage, name

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Login without Connect Permission somehow managed to become db_owner!?

I tried asking the same question in other forum but couldn't get an answer.

I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'

What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!

also post the reslut of

sp_helplogins 'yy'

sp_helpuser 'yy'

Madhu

|||

You can also use the following two queries to determine the role memberships and the permissions associated with login yy:

selectsuser_name(role_principal_id),suser_name(member_principal_id)fromsys.server_role_memberswhere member_principal_id =suser_id('yy')
select permission_name, class_desc, major_id fromsys.server_permissionswhere grantee_principal_id =suser_id('yy')

Thanks
Laurentiu

|||

Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.

Madhu,

i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':

LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no

and the 2nd result set is empty.

Laurentiu,

I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.

|||

Madhu

|||

Check the context you are logged in:

selectsuser_name()

and also check who owns the database by querying sys.databases:

selectname,suser_sname(owner_sid)fromsys.databases

Thanks
Laurentiu

|||

Madhu,

Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.

Laurentiu,

sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:

Use ApplicationDB
GO
select suser_name()

Result: yy

And also i run the following:
select name, suser_sname(owner_sid) from sys.databases

Result: indicating my applicationDB is own by domain\Administrator

I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?

|||

I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:

select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.

Thanks
Laurentiu

|||

Laurentiu,

Returns me:

permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5

this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.

|||See BOL under of DB Roles

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

You must have changed your "public" role on the 2nd database, which the user does not see.

|||

The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?

Thanks
Laurentiu

PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.

|||

Tom,

i have compare my public role between the accessible application db and other none accessible db, i find no difference.

Laurentiu,

i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q

|||

I'm out of ideas. Here are the two suggestions I have left:

Thanks
Laurentiu

|||

Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:

SELECT*FROMsys.login_tokenORDERBYtype, usage,name

SELECT*FROMsys.user_tokenORDERBYtype, usage,name

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Login without Connect Permission somehow managed to become db_owner!?

I tried asking the same question in other forum but couldn't get an answer.

I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'

What could be the problem i am facing? the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!

also post the reslut of

sp_helplogins 'yy'

sp_helpuser 'yy'

Madhu

|||

You can also use the following two queries to determine the role memberships and the permissions associated with login yy:

Thanks
Laurentiu

|||

Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.

Madhu,

i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':

LoginName SID DefDBName DefLangName AUser ARemote
- -- -- - -
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no

and the 2nd result set is empty.

Laurentiu,

I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.

|||

Madhu

|||

Check the context you are logged in:

select suser_name()

and also check who owns the database by querying sys.databases:

select name, suser_sname(owner_sid) from sys.databases

Thanks
Laurentiu

|||

Madhu,

Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.

Laurentiu,

sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:

Use ApplicationDB
GO
select suser_name()

Result: yy

And also i run the following:
select name, suser_sname(owner_sid) from sys.databases

Result: indicating my applicationDB is own by domain\Administrator

I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy?

|||

I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:

select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.

Thanks
Laurentiu

|||

Laurentiu,

Returns me:

permission_name class_desc major_id
-- -
VIEW ANY DATABASE SERVER 0
CONNECT ENDPOINT 2
CONNECT ENDPOINT 3
CONNECT ENDPOINT 4
CONNECT ENDPOINT 5

this is interesting, "view any database" means yy will hav permission to view all database? BUT, in fact there's 2 application database, and yy manage to view only ONE of it.

|||See BOL under of DB Roles

Every database user belongs to the public database role. When

a user has not been granted or denied specific permissions on a securable, the

user inherits the permissions granted to public on that securable.

You must have changed your "public" role on the 2nd database, which the user does not see.

|||

The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case?

Thanks
Laurentiu

PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.

|||

Tom,

i have compare my public role between the accessible application db and other none accessible db, i find no difference.

Laurentiu,

i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this? Thank q

|||

I'm out of ideas. Here are the two suggestions I have left:

Thanks
Laurentiu

|||

Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner.Can you please run the following query using the affected context:

SELECT * FROM sys.login_token ORDER BY type, usage, name

SELECT * FROM sys.user_token ORDER BY type, usage, name

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Wednesday, March 7, 2012

login windows authentication problem

Hi,

I have a problem with sql server 2005 express, when i try to connect to an sql database it says login: is not a trusted connection to an sql database. I have gone into reg edit and changed the authentication setting from windows oly mode 1 to mixed mode 2. It still has the same problem, does anyone have any idea what the problem may be?

kind regards

Chris J

Did you restart the service ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Login to MSDE on Win 98

Hi all,

I have the the problem with logging to MSDE using SQL-DMO interface. On the
W2K with LoginSecure=TRUE everything is OK (connect is made without any
parameters)

But when LoginSecure=FALSE (on Win 98)

connect( 'instance_name', 'sa', 'sa_password' )

then it returns error message :

I install MSDE on Win 98 with parameters :

INSTANCENAME='instance_name'
SECURITYMODE=SQL
SAPWD='sa_password'

Server runs with given instance name.

Who knows why I can not login to MSDE ?

TIA,

Marek Powichrowski"Marek Powichrowski" <marekp@.telbank.pl> wrote in message
news:bv81ss$hnp$1@.korweta.task.gda.pl...
> Hi all,
> I have the the problem with logging to MSDE using SQL-DMO interface. On
the
> W2K with LoginSecure=TRUE everything is OK (connect is made without any
> parameters)
> But when LoginSecure=FALSE (on Win 98)
> connect( 'instance_name', 'sa', 'sa_password' )
> then it returns error message :
> Login failed for user 'sa'. Reason : Not associated with a trusted SQL
> I install MSDE on Win 98 with parameters :
> INSTANCENAME='instance_name'
> SECURITYMODE=SQL
> SAPWD='sa_password'
> Server runs with given instance name.
> Who knows why I can not login to MSDE ?
> TIA,
> Marek Powichrowski

The error is slightly strange, since it suggests MSDE is running in Windows
authentication mode, however that isn't supported on Windows 98. Can you
connect to the MSDE instance locally using osql, for example?

osql -S instance_name -U sa -P sa_password

If you can connect like this, then the issue is probably somewhere in your
SQLDMO code, and perhaps you could post the part which makes the connection.
If this doesn't work, then could you post the error message? And did you
install the latest MSDE version, ie. SP3a?

Simon|||>
> The error is slightly strange, since it suggests MSDE is running in
Windows
> authentication mode, however that isn't supported on Windows 98. Can you
> connect to the MSDE instance locally using osql, for example?
> osql -S instance_name -U sa -P sa_password
> If you can connect like this, then the issue is probably somewhere in your
> SQLDMO code, and perhaps you could post the part which makes the
connection.
> If this doesn't work, then could you post the error message? And did you
> install the latest MSDE version, ie. SP3a?

Cause of error was setting LoginSecure to TRUE for the connection made on
the Win 98. When I change LoginSecure to FALSE then connection ends
succesfully (for proper server name user name and password) . So I can
create database using script generated by Enterprise Manager from my
computer and can atatch existing database to MSDE without Enterprise Manager
in the easy way (few lines of code).

Thanks for your support.

Marek Powichrowski

Login to MSDE

Hi all,
I have the the problem with logging to MSDE using SQL-DMO interface. On the
W2K with LoginSecure=TRUE everything is OK (connect is made without any
parameters)
But when LoginSecure=FALSE
connect( 'instance_name', 'sa', 'sa_password' )
it returns error message :
Login failed for user 'sa'. Reason : Not associated with a trusted SQL
I install MSDE Win 98 with parameters :
INSTANCENAME='instance_name'
SECURITYMODE=SQL
SAPWD='sa_password'
Server runs with given instance name.
Who knows why I can not login to MSDE ?
TIA,
Marek PowichrowskiCheck out article 319930, especially the section entitled:
Enable Mixed Mode Authentication After Installation
to verify that MSDE is in fact running in mixed mode.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Uytkownik "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> napisa w
wiadomoci news:KWta2ad5DHA.824@.cpmsftngxa07.phx.gbl...

quote:

> Check out article 319930, especially the section entitled:
> Enable Mixed Mode Authentication After Installation
> to verify that MSDE is in fact running in mixed mode.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Thanks for your support.
Marek Powichrowski

Login to db

Hi
I have two programs that connect to the same db using exact same conncetion
string but only one manages to connect.
Any explanations
Thanks
Sam
Can you provide a little more details like maybe the error message? Have
you checked to see if they are both using TCP or named piepes etc?
Andrew J. Kelly SQL MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>
|||Hi,
Could you enable the SQL Profiler and run the applications seperately and
check what happends exactly.
Does both your application uses same login, database ?
Thanks
Hari
MCDBA
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:#p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>

Login to db

Hi
I have two programs that connect to the same db using exact same conncetion
string but only one manages to connect.
Any explanations
Thanks
SamCan you provide a little more details like maybe the error message? Have
you checked to see if they are both using TCP or named piepes etc?
Andrew J. Kelly SQL MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%23p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>|||Hi,
Could you enable the SQL Profiler and run the applications seperately and
check what happends exactly.
Does both your application uses same login, database ?
Thanks
Hari
MCDBA
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:#p5GRLUfEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have two programs that connect to the same db using exact same
conncetion
> string but only one manages to connect.
> Any explanations
> Thanks
> Sam
>

login to database failed...

Hi,

I am using VS2003 with SQLExpress on my local machine. I am trying to configure an app to connect to a loca sql express database. The following is
the connection string that I am using and the error message that I am receiving.

Any ideas what the problem is?

Cannot open database "Test1" requested by the login. The login failed. Login failed for user 'IKITSCH\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "Test1" requested by the login. The login failed. Login failed for user 'IKITSCH\ASPNET'.

Source Error:

Line 1600: {
Line 1601: SqlConnection conSql = new SqlConnection(ConnectionString);
Line 1602: conSql.Open();
Line 1603: return conSql;
Line 1604: }

Source File: c:\inetpub\wwwroot\testsite\classes\config.cs Line: 1602

Stack Trace:

[SqlException: Cannot open database "Test1" requested by the login. The login failed.
Login failed for user 'IKITSCH\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
eStreamBG.Dating.Classes.DB.Open() in c:\inetpub\wwwroot\testsite\classes\config.cs:1602
eStreamBG.Dating.Classes.DBSettings.Get(String Key) in c:\inetpub\wwwroot\TestSite\Classes\DBSettings.cs:125
eStreamBG.Dating.Classes.DBSettings.Get(String Key, String Default) in c:\inetpub\wwwroot\TestSite\Classes\DBSettings.cs:75
eStreamBG.Dating.Classes.Misc.get_SiteEmail() in c:\inetpub\wwwroot\testsite\classes\config.cs:1719
eStreamBG.Dating.Classes.EmailQueueItem.Create(String to, String subject, String body) in c:\inetpub\wwwroot\TestSite\Classes\Email.cs:224
eStreamBG.Dating.Classes.ExceptionLogger.Log(String source, Exception ex) in c:\inetpub\wwwroot\TestSite\Classes\Exceptions.cs:122

Many Thanks!

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

You are using integrated security for authentication to the SQL Server database.

ASP.Net runs with the identity of the ASPNET user account by default and this user account has limited rights.

Try to avoid integrated security and specify the user name and password for the SQL Connection String instead.

And make sure you DB name isTest1 not test1 Smile

|||That means the login account used to open the connection does not have access to the requested database Test1. Since the connection use Trust_connection and Impersonate is not enabled, IIS?use?its?account?machinename\ASPNET?to?connect?to?database?then?causes?the?failure.?To?solve?this,?open?Management?Studio->connect?to?SQLExpress->navigate?to?Security->Logins->check Properties of IKITSCH\ASPNET->switch?to?User?Mapping?tab->add?mapping?to?the?Test1?database?and?give?sufficient?permissions?to?the?mapped?database?user?for?future?operations?on?Test1?database.?

You can download Management Studio Express from here:

http://msdn.microsoft.com/vstudio/express/sql/download/|||

Hi,

Many Thanks!

|||

Hello,

I am also experiencing the same problem as 'chemystery', and I too have used integrated security in my connection string for authentication to the SQL Server.

I have contacted the DB and server administrators to find out if they can give me the username and password so that I can include it in my connectionstring but they have refused as they say it breaches their security protocols.

Is there another way around this that will allow integrated security and allow access to the DB?

Thanks in advance!

|||Then you have 2 options:
Walkthrough: Encrypting Configuration Information Using Protected Configuration

Or?ask the database host to add your Windows account?to?SQL?logins?and?give?it?proper?permissions,?so?that?you?can?the?same?Windows?Identity?when?opening?connection?with?Integrated?Security.?To?ensure?to?use?your?account?to?open?conneciton?for?each?web request,?you?can?use?impersonation?with?credentital, please refer to: identity Element

Friday, February 24, 2012

Login Properties: Deny, Disable, Lock Out? Which do I use?

Hey again!

I have a SQL login, and I want to prevent the login from logging into SQL Server.

From SSMS, I connect to the Database Engine, expand Security, Logins.
I right-click the login in question and select Properties. I select the Status page.

So I see I can deny, disable and lock out this login.
What is the difference between these three options?

Also, shouldn't the checkbox "Login is locked out" be checkable?
Currently, it's unchecked/disabled and I cannot alter this value.

I performed a cross-reference of the possible combinations:

SQL Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
UserName (valid) Grant Enabled No 0
UserName (valid) Grant Enabled Yes 18486
UserName (valid) Grant Disabled No 18470
UserName (valid) Grant Disabled Yes 18470
UserName (valid) Deny Enabled No 18456
UserName (valid) Deny Enabled Yes 18486
UserName (valid) Deny Disabled No 18470
UserName (valid) Deny Disabled Yes 18470
UserName (garbage) Grant Enabled No 18456
UserName (garbage) Grant Enabled Yes 18456
UserName (garbage) Grant Disabled No 18456
UserName (garbage) Grant Disabled Yes 18456
UserName (garbage) Deny Enabled No 18456
UserName (garbage) Deny Enabled Yes 18456
UserName (garbage) Deny Disabled No 18456
UserName (garbage) Deny Disabled Yes 18456
(garbage) (garbage) Either Either No 18456

NT-Logins:

FQ Login ID Password Status Enabled? Lock? @.@.ERROR
-- -- --
DOMAIN\UserName N/A Grant Enabled N/A 0
DOMAIN\UserName N/A Deny Enabled N/A 18456
DOMAIN\UserName N/A Grant Disabled N/A 18470
DOMAIN\UserName N/A Deny Disabled N/A 18470

where @.@.ERROR
0 = Connects
18456 = Login failed for user 'UserName'.
18470 = Login failed for user 'UserName'. Reason: The account is disabled.
18486 = Login failed for user 'UserName' because the account is currently locked out. The system administrator can unlock it.

(Upon third garbage-password, the SQL-account is locked.)

|||

A plain 18456 "login failed" message could mean
- the user is using an incorrect username
- the user is using an incorrect password
- the user isn't defined to the datasource
- the user is denied access.

Based on these findings, I like the "disabled" setting over "denied." If a user calls me with an 18470 message, I have some confidence in that the user connecting with a valid username and password.

Monday, February 20, 2012

Login problem

I'm trying to connect to sql server 2005 & i'm getting the following error message

"Cannot open database 'XXXX' requested by the login. The login failed. Login failed for user Domain\User"

Ok i tried enabling disabling client protocols , i tried all Surface area configuration settings

I'm running Vista Ultimate With 2 processes & 4 cores

previously i was using xp and it worked untill i dettached & attached

So somewhere between Vista, 4 Cores & Attach Dettach the problem lies

I also tried changing sql server manegment studio security logons & users but that didn't help

Did you check if that user still exists in a domain of the network?|||

check the defult datbase of this particular user. Each user has a default database defined. check which is the default database for this user. it the database does not exists , change the default database of the user to any existing database

Madhu

|||

I suspect that you are using Vista’s UAC (User Account Control) feature. Under this feature, the Windows security token you are using to connect to SQL Server is not really a privileged user, and most likely you are getting access as a regular Windows user (I also assume you installed SQL Server Express in Vista).

Try running the following statement to confirm if my assumption is correct:

SELECT name, usage, type FROM sys.login_token ORDER BY usage, type, name

If you are running as a UAC token, you will see at least one row like the following:

BUILTIN\AdministratorsDENY ONLYWINDOWS GROUP

You should also notice that you are not a member of sysadmin.

For more detailed information on running SQL Server on Windows Vista, please visit BOL (http://msdn2.microsoft.com/en-us/library/Aa905868.aspx)

Quick workaround: Connect using SA login, and explicitly add yourself (or/and any other Windows user or groups you need) to sysadmin server role.

I hope this information helps.

-Raul Garcia

SDE/T

SQL Server Engine

|||

OK thanks to all for trying to help but none of the suggestions were helpfull

as it turns out i changed the name of the database in sql server management studio and then changed it back to the original and it just worked

since i had the excact same symptom on the computer where i was deploying to i tried it and it worked

I think it's very strange for this behaviour to be occuring some of the reasons i think they were occuring is because the attached database

was in the project or the bin\debug folder, also attaching and detaching the database creates problems for the user app to login

|||

I see. Most likely this is the default database for the login, and as it was not available, the connection was terminated early.

As a workaround in the future, you can explicitly specify a DB to connect to override the default DB. I would recommend connecting to master (master should always exist) and fix the default DB problem from there.

Thanks a lot for letting us know what was the root cause of the problem.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Nothing strange in this.. if the default database is not there , you will not be able to login which was already mentioned in the previous post

Madhu

Wednesday, March 21, 2012

LogReader unable to connect to the publisher

Logreader down question

Monday, March 19, 2012

Logon Failure during exporting

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Logon Error: 18456, Severity: 14, State: 8 - NEED HELP!!!!

Friday, March 9, 2012

loging in to sql express

Login without Connect Permission somehow managed to become db_owner!?

Login without Connect Permission somehow managed to become db_owner!?

Login without Connect Permission somehow managed to become db_owner!?

Login without Connect Permission somehow managed to become db_owner!?

Wednesday, March 7, 2012

login windows authentication problem

Login to MSDE on Win 98

Login to MSDE

Login to db

Login to db

login to database failed...

Friday, February 24, 2012

Login Properties: Deny, Disable, Lock Out? Which do I use?

Monday, February 20, 2012

Login problem

sql login permissions

Blog Archive

About Me