Attach remote DB file: mode

Showing posts with label mode. Show all posts

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

Hello!
I am trying to get a SQL server that was configured by someone else up
and running the backup and maintainance jobs from within SQL server
2000. when the job runs it backs up all but 3 of the databases, the 3
it doesnt is our ASDB (our arcserv) database, master and model.
the error that shows for the asdb database is ..
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
The one that shows for the others is this...
12] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[13] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Database state cannot be changed
while other users are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
[14] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL Server Driver][SQL Server]Repair statement not processed.
Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Can anyone advise me on how to resolve this issue?
Thanks in advance!
/ehrenWingnut wrote:
> Hello!
> I am trying to get a SQL server that was configured by someone else up
> and running the backup and maintainance jobs from within SQL server
> 2000. when the job runs it backs up all but 3 of the databases, the 3
> it doesnt is our ASDB (our arcserv) database, master and model.
> the error that shows for the asdb database is ..
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> The one that shows for the others is this...
>
> 12] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [13] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> while other users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> failed.
> [14] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> Database needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Can anyone advise me on how to resolve this issue?
> Thanks in advance!
> /ehren
>
In your maintenance plan, turn off the "repair problems" option...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, I will give that a try.
On Jan 29, 9:38 am, Tracy McKibben <t...@.realsqlguy.com> wrote:
> Wingnut wrote:
> > Hello!
> > I am trying to get a SQL server that was configured by someone else up
> > and running the backup and maintainance jobs from within SQL server
> > 2000. when the job runs it backs up all but 3 of the databases, the 3
> > it doesnt is our ASDB (our arcserv) database, master and model.
> > the error that shows for the asdb database is ..
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > The one that shows for the others is this...
> > 12] Database master: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [13] Database model: Check Data and Index Linkage...
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Database state cannot be changed
> > while other users are using the database 'msdb'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
> > statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
> > failed.
> > [14] Database msdb: Check Data and Index Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
> > [ODBC SQL Server Driver][SQL Server]Repair statement not processed.
> > Database needs to be in single user mode.
> > The following errors were found:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> > processed. Database needs to be in single user mode.
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> > Can anyone advise me on how to resolve this issue?
> > Thanks in advance!
> > /ehrenIn your maintenance plan, turn off the "repair problems" option...
> --
> Tracy McKibben
> MCDBAhttp://www.realsqlguy.com- Hide quoted text -- Show quoted text -

auto backup jobs wont run - not in single user mode

Thursday, March 22, 2012

Authentication mode, connecting from webpage

Hello,
I need help setting up SQL Server Express edition.
What I did so far:
1) Downloaded and installed it with windows authentication
2) Installed SQL Server Management Studio Express
3) Created a database
4) Realized I needed SQL Authentication for connecting via php page.
5) Followed these instructions to enable SQL Authentication
Now that SQL Authentication is enabled, how do I create a user account that I can use in php pages to connect to the database?
Thanks,
Kurt

Hey Kurt. See the documentation in Books Online for the following commands:

CREATE LOGIN

CREATE USER

The create login statement will create a user on your server, then run the create user statement to allow that user access to the database(s) on your system.

HTH,

|||Hello,
Ok thanks, I got my user created with the following. I also realized its the same as creating a user in SQL Management Studio Express.
sqlcmd -S .\SQLEXPRESS
1>CREATE LOGIN myuser WITH PASSWORD = 'mypassword';
2>GO
Is there anything else I have to change for php to beable to connect with this user?
Thanks,
Kurt|||

You need to run the CREATE USER command to give the login you just created access to the database(s) you want it to have access to. Then you have to provide the appropriate permissions to that user within the database in question (i.e. select from tables, update tables, delete tables and records, etc., etc.)

|||Hi Chad,
I keep getting an error when I try to connect from a webpage. For the server can I use localhost?
Warning: mssql_connect(): Unable to connect to server: localhost in D:\wwwRoot\db\connect.php on line 18

Thanks,
Kurt

Authentication mode selection

There are two different tasks I would like to serve with SSEE, both case the users are changing seats. I think SQL Server Authentication would be better for lightweight user change but I have read everywhere that Windows Authentication Mode is the preferred way to go.

Is there any significant weakness in SQL Server authentication mode - security, work with stored procedures (CLR) or anything?

With SQL Server 2005, there has been a significant increase in the robustness of SQL Security. Especially if you 'enforce password policy' -including enforcing expiration policies on the SQL Accounts. (And your domain/AD has password policies defined and enforced.)

However, it the situation of users 'changing seats', Windows authentication 'should' still work fine.

|||

Before choosing Authentication mode there are few things to be considered. From the security point of view , Windows authentication is better. You can have all the security policy as in the OS level and user no need to remember multiple user name and password. In this case the main disadvantage is that it can not support multi OS platform. ie from UNIX machine you can not access this database. and many legacy software runs from multiplatform. This is the main reason we go for Mixed mode.

Madhu

|||

Hello.....

Best practice is to choose windows Authentication for SQL Server 2005 domain environment. If your user are using legacy application then better to go for Mixed Authentication.

No, there is nothing weak to choose mixed mode authentication. This type of authentication is needed for legacy applications. Application which are running on other platform(except Windows platform) and needs to connect to SQL Server.

Authentication Mode question from a command line junkie

What is the syntax from to change the authentication mode from inside of the server(ie: osql)? And what system tables can be queried to identify which mode the server is currently running in?
To check the mode use
select serverproperty('IsIntegratedSecurityOnly')
or
exec master..xp_loginconfig 'login mode'
You can use the undocumented xp_regwrite/xp_instance_regwrite to change the
registry key that contols this (which is what EM does anyway) or use SQLDMO
but it needs a SQL Service restart to take effect e.g.
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'LoginMode', N'REG_DWORD', 2
'Set security mode to mixed where oSQL is a SQLServer object
Set oSEC = oSQL.IntegratedSecurity
oSEC.SecurityMode = 2
0 or 2 = Mixed
1 = NT only
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:C3B38A1E-32D5-4891-947F-56E5A9857A30@.microsoft.com...
> What is the syntax from to change the authentication mode from inside of
the server(ie: osql)? And what system tables can be queried to identify
which mode the server is currently running in?
sql

Authentication Mode question from a command line junkie

What is the syntax from to change the authentication mode from inside of the
server(ie: osql)? And what system tables can be queried to identify which
mode the server is currently running in?To check the mode use
select serverproperty('IsIntegratedSecurityOnly
')
or
exec master..xp_loginconfig 'login mode'
You can use the undocumented xp_regwrite/xp_instance_regwrite to change the
registry key that contols this (which is what EM does anyway) or use SQLDMO
but it needs a SQL Service restart to take effect e.g.
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLSe
rver',
'LoginMode', N'REG_DWORD', 2
'Set security mode to mixed where oSQL is a SQLServer object
Set oSEC = oSQL.IntegratedSecurity
oSEC.SecurityMode = 2
0 or 2 = Mixed
1 = NT only
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:C3B38A1E-32D5-4891-947F-56E5A9857A30@.microsoft.com...
> What is the syntax from to change the authentication mode from inside of
the server(ie: osql)? And what system tables can be queried to identify
which mode the server is currently running in?

Authentication Mode question from a command line junkie

What is the syntax from to change the authentication mode from inside of the server(ie: osql)? And what system tables can be queried to identify which mode the server is currently running in?To check the mode use
select serverproperty('IsIntegratedSecurityOnly')
or
exec master..xp_loginconfig 'login mode'
You can use the undocumented xp_regwrite/xp_instance_regwrite to change the
registry key that contols this (which is what EM does anyway) or use SQLDMO
but it needs a SQL Service restart to take effect e.g.
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'LoginMode', N'REG_DWORD', 2
'Set security mode to mixed where oSQL is a SQLServer object
Set oSEC = oSQL.IntegratedSecurity
oSEC.SecurityMode = 2
0 or 2 = Mixed
1 = NT only
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dean" <anonymous@.discussions.microsoft.com> wrote in message
news:C3B38A1E-32D5-4891-947F-56E5A9857A30@.microsoft.com...
> What is the syntax from to change the authentication mode from inside of
the server(ie: osql)? And what system tables can be queried to identify
which mode the server is currently running in?

Authentication Mode problems

Hi Everyone

I changed the authentication mode from SQL Server Authentication into Windows of my application (Web application - ASP.NET 1.1, SQL Server 2000, C#). I changed the SqlServer into Windows only mode authentication.

The SQL server now contains all the "windows users" as users of the database (Logins as well as Users).

But I faced the problem of 'login failed for NT Authority\Network Service'. So I added the Network Service as user. The login problem is disappeared, but when I'm tracing through the SQL Profiler I can see only the Network Service as user and not my windows username\SQL server login name.

what should I do for displaying the windows username\Sql login name in the profiler instead of "Network Service". Please help me.

Thanks in advance

Fraijo

<P>

The network service is showing up, because the application is running using those credentials not user credentials. If you want it to use user as account, then you need to login as those users which is only possible if they have permisson on the resource.

|||

I'm sorry

it is not solved.. Because I checked all the user rights of my login. is there any other possible way to find the user credentials are suitable for running my application? Please share.

thanks

|||

Impersonation is one way but then you are back to the same issue.

|||

Hi fraijo,

Sorry that i'm a little confused after reading your question. It seems to me that you are able to access to the database successfully after you added the NETWORK SERVER as one database user. Why do you want to use windows username\SQL server login name instead of network service account?

My suggestion could be wrong.But as far as i know, NETWORK SERVICE is the default winodws account used by ASP.NET to access to the database,so, you cannot change this account to another one.

Hope my suggestion could help

|||

As you said I can access the database. But in my application it is checking authorization by taking the windows usernames. So I need the windows username as users for the SQL Server too..

thanks in advance

Fraijo

Authentication mode / configuration issue

I'm having a little issue here with the MSSQLServer service which installs as part of the BlackBerry Enterprise Server. I can connect to it and run queries on it via Query Analyzer, but when I attempt to have a PHP script query against it, I get an error stating that the login failed because the user is not associated with a trusted SQL connection (severity 14). I've seen this before with other servers, and it was always a result of the server not being in mixed authentication mode.

So the question is this: How do I change it? There doesn't appear to be an Enterprise Manager or anything equivalent installed. Digging through the SQL Server folder, the only thing I saw that might have been useful is the SQL Server Network Utility located at Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.EXE. This utility appears to only let me disable/enable protocols however, nothing about authentication.

I checked the Service Manager as well and didn't see anything of use there. Any ideas how I can do this?

It looks like I might have figured this one out. Using a copy of Enterprise Manager that I had installed on my machine, I added the MSSQL server installed on the BlackBerry server and changed its authentication properties that way. It looks like I'll have to tweak with some permissions for the individual databases but I think I've got it now.

authentication mode

will not let me connect using SQL Server authentication
mode?
If you're using SQL Server's authentication mode you need to be sure to add
a login for the user ID to SQL Server and grant that login rights to the
database that it needs to access. If you;re connecting from an ADO or
ADO.NET application you also need to pass the user ID and password to SQL
Server as part of the connection string.
Michael Otey
"denise" <anonymous@.discussions.microsoft.com> wrote in message
news:2b93001c4691a$a4309510$a601280a@.phx.gbl...
> will not let me connect using SQL Server authentication
> mode?
|||Check to see if your server is in Mixed Mode. Meaning that it will accept
both Standard Authentication and
WIndows Authentication.
This article covers MSDE which is basically points to the same place in the
registry.
319930 How To Connect to Microsoft Desktop Engine
http://support.microsoft.com/?id=319930
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
sql

authentication mode

will not let me connect using SQL Server authentication
mode?If you're using SQL Server's authentication mode you need to be sure to add
a login for the user ID to SQL Server and grant that login rights to the
database that it needs to access. If you;re connecting from an ADO or
ADO.NET application you also need to pass the user ID and password to SQL
Server as part of the connection string.
Michael Otey
"denise" <anonymous@.discussions.microsoft.com> wrote in message
news:2b93001c4691a$a4309510$a601280a@.phx
.gbl...
> will not let me connect using SQL Server authentication
> mode?|||Check to see if your server is in Mixed Mode. Meaning that it will accept
both Standard Authentication and
WIndows Authentication.
This article covers MSDE which is basically points to the same place in the
registry.
319930 How To Connect to Microsoft Desktop Engine
http://support.microsoft.com/?id=319930
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

authentication Mode

I am developing a client/server application using sql server as the back end
.
This app must allow for two login scenarios other than the usual one
user/one workstation. First, the app must allow users to log in from any
machine on the network even when using a machine running under a different
users login. Second, the app must be available to someone that does not hav
e
a windows login, eg a field worker that needs to access the app occasioniall
y
using a machine running under a different users login. For these reasons, I
have been using mixed mode authentication which does the job. Since all the
documentation seems to recommend Windows authentication mode and mixed mode
is for backward compability, am I missing something here? Can I handle the
two scenarios, particularly the second using Windows Authentication?
JBConsider using an Application Role rather than user level security.
Look in Books Online for "Establishing Application Security and Application
Roles".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JB" <JB@.discussions.microsoft.com> wrote in message
news:AA8A22DC-D007-4CBE-BFF8-758693B9FCC0@.microsoft.com...
>I am developing a client/server application using sql server as the back
>end.
> This app must allow for two login scenarios other than the usual one
> user/one workstation. First, the app must allow users to log in from any
> machine on the network even when using a machine running under a different
> users login. Second, the app must be available to someone that does not
> have
> a windows login, eg a field worker that needs to access the app
> occasionially
> using a machine running under a different users login. For these reasons,
> I
> have been using mixed mode authentication which does the job. Since all
> the
> documentation seems to recommend Windows authentication mode and mixed
> mode
> is for backward compability, am I missing something here? Can I handle
> the
> two scenarios, particularly the second using Windows Authentication?
> JB|||That's hard to say as this line doesn't make sense:
"Second, the app must be available to someone that does not
have a windows login, eg a field worker that needs to
access the app occasionially using a machine running under
a different users login"
So what login is the "different users login" - but the user
doesn't have a login?
It really depends on what login is being used, if the field
work is accessing a machine in a domain, if it's multiple
domains, depends on trusts that may or may not be setup,
etc.
In terms of the other issue, Machines and logins are two
different things. If I have my windows login setup for
access to a SQL Server box in my domain, it doesn't matter
what machine I use. If I login into the network, that's the
credentials that are used no matter what machine I may be
logged into.
If users are logging into the domain with all different
logins, accessing network resources with various logins then
you have a security mess at the network level which will
lead to security messes in SQL Server as well when
implementing Windows authentication.
-Sue
On Fri, 22 Sep 2006 10:17:01 -0700, JB
<JB@.discussions.microsoft.com> wrote:

>I am developing a client/server application using sql server as the back en
d.
> This app must allow for two login scenarios other than the usual one
>user/one workstation. First, the app must allow users to log in from any
>machine on the network even when using a machine running under a different
>users login. Second, the app must be available to someone that does not ha
ve
>a windows login, eg a field worker that needs to access the app occasionial
ly
>using a machine running under a different users login. For these reasons,
I
>have been using mixed mode authentication which does the job. Since all th
e
>documentation seems to recommend Windows authentication mode and mixed mode
>is for backward compability, am I missing something here? Can I handle the
>two scenarios, particularly the second using Windows Authentication?
>JB

Authentication Mode

If I have a server with windows only authentication mode, How can I login
in Sql Server 2000 using SA or GUEST account? It is possible?no way, only if you switch oveer the authentication mode via the registry.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mauro" <Mauro@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F09557A1-921C-4887-8643-CDC7FC0DDEBB@.microsoft.com...
> If I have a server with windows only authentication mode, How can I
> login
> in Sql Server 2000 using SA or GUEST account? It is possible?

Tuesday, March 20, 2012

Authentication

Hello All,

i was wondering what is the main difference between the windows
authentication and mixed mode authentication??
according to security recommendations, we should enable windows
authentication, rather than mixed one, i don get the point why do we
refuse the mixed mode authentication, although it includes windows
authentication together with an extra layer of defense by the aid of an
extra authentication mechanism, sql authentication.

i hope i can find out why??
if anyone can recommend me some papers or books, i'll be thankfull but
i hope it explains from the security point of view.

Thanx for time and sorry for interruption(Eng.Rana@.gmail.com) writes:
> i was wondering what is the main difference between the windows
> authentication and mixed mode authentication??
> according to security recommendations, we should enable windows
> authentication, rather than mixed one, i don get the point why do we
> refuse the mixed mode authentication, although it includes windows
> authentication together with an extra layer of defense by the aid of an
> extra authentication mechanism, sql authentication.

No, mixed mode does not give you any extra layer of protection.

In the beginning, SQL Server only had one means of authetication: username
and password stored in the master database in SQL Server. To connect to SQL
Server, you needed to specify username and password. This is today known as
SQL authentication.

Later Microsoft added Windows authentication which permits you to log in
with your Windows credentials. This is known as "Windows authenticiation",
"Trusted connnection" or "Integrated Security".

In SQL 6.x you had three choices: Windows authenticaton only, SQL
authentication only or both. With SQL 7, Microsoft removed the alternative
SQL authentication only.

Windows Authentication is generally regarded as more secure in SQL 2000,
because SQL Server does not have any means to check password strength,
lock accounts with many failed logins etc. Also, it's fairly easy to
crack a password sent over the wire, as the "encryption" is just a mild
form of obfustication. Some of these issues has been resolved in SQL 2005,
provided that you use Widows 2003.

However, Windows authentication requires that both client and server are
in the same domain, or are in domains that trust each other. Mixed mode
is also conventient when you work in a development environment and need
to load stored procedures etc from a privileged account, but you need to
test the application as a low-priv user.

--
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|||thats a gr8 help, i really appreciate it :).

but i was wondering, what if the client and server were of different
domains, shall we be using only the SQL authentication only?

if yes, is there any other situation where we can't use windows
authentication?

thanx again for ur gr8 help :D|||(Eng.Rana@.gmail.com) writes:
> thats a gr8 help, i really appreciate it :).
> but i was wondering, what if the client and server were of different
> domains, shall we be using only the SQL authentication only?

Unless you can set up some trust between them you need to use SQL
authentication.

Here I need to add this is a question that requires good knowledge about
Windows networking, which I do not possess.

> if yes, is there any other situation where we can't use windows
> authentication?

If you don't have a domain at all, but only a Workgroup, Windows
authentication can be difficult. Username and password must match,
but this may not be enough. For instance, I run virtual machines on
my comnputer at home, and from the host machine I connect to SQL Server
on the virtual machines with Windows authentication, and and Windows
authentication also works between virtual machine. But it does not
work from virtual machines to the SQL Server instances on the host
machine.

If the client comes from a non-Windows OS it may be even more difficult
to use Windows authentication. Obviously.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

how can i only enforce sql authenticaion, although the only two options
available are Windows authentication only or mixed mode.

this means that there always has to be trust between domains or that
they be on the same domain.

else how can enforce SQL authentication only!!!!!

sorry for all these questions
i really appreciate ur help
Thanx for time|||(Eng.Rana@.gmail.com) writes:
> how can i only enforce sql authenticaion, although the only two options
> available are Windows authentication only or mixed mode.
> this means that there always has to be trust between domains or that
> they be on the same domain.
> else how can enforce SQL authentication only!!!!!

Not sure that I understand your question. You cannot configure SQL Server
to not permit Windows authentication at all. But if you put the SQL Server
machine in its own domain (or a workgroup), the only way to log in with
Windows authnetication is on the local machine.

Note that SQL authetnication is not dependent on domains trusting each
other.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

>> Unless you can set up some trust between them you need to use SQL
>> authentication.
>how can i only enforce sql authenticaion, although the only two options
>available are Windows authentication only or mixed mode.
>this means that there always has to be trust between domains or that
>they be on the same domain.
>else how can enforce SQL authentication only!!!!!

Hi Eng.Rana,

Though you can't disable the mechanism for Windows authentication, you
can render it unfunctional by granting nobody the right to login with
Windows authentication.

Windows authentication doesn't mean that every domain user suuddenly has
the right to connect to SQL Server - you have to explicitly allow this
to individual Windows accounts or groups. In SQL Server 2000, you used
the sp_grantdbaccess stored pprocedure (or some graphical tool) for
this; in SQL Server 2005, this stored procedure is replaced by the
CREATE LOGIN xxx FROM WINDOWS command..

Note that by default, all windows accounts that are in the
builtin/administrators group have access to the DB using Windows
authentication. If you realy want to force everyone to use SQL
authentication, you'll have to remove these logins (using
sp_revokedbaccess or DROP LOGIN). I have never tried if this works,
because I'm scared that I'll forget my sa password and never be able to
regain access to the DB. <g
--
Hugo Kornelis, SQL Server MVP

Monday, March 19, 2012

Authenication Mode

I've been reading on the authentication modes - Windows and mixed (sql
server). I'd like some comments on the pros and cons of each and which
most people use.
As I understand it with Windows your windows login is used to get you into
the SQL server. What happens after that - do you still need to assign
users to the database?
Thanks.
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to emailHi,
Yes. Login will just allows you to authenticate to SQL Server. After that
you will have associate the login with a database user and
assign permission for that database user to access each of the required
databases.
Thanks
Hari
SQL Server MVP
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23yDEYNu1GHA.1256@.TK2MSFTNGP02.phx.gbl...
> I've been reading on the authentication modes - Windows and mixed (sql
> server). I'd like some comments on the pros and cons of each and which
> most people use.
> As I understand it with Windows your windows login is used to get you into
> the SQL server. What happens after that - do you still need to assign
> users to the database?
> Thanks.
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email|||Brett I.Holcomb
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23yDEYNu1GHA.1256@.TK2MSFTNGP02.phx.gbl...
> I've been reading on the authentication modes - Windows and mixed (sql
> server). I'd like some comments on the pros and cons of each and which
> most people use.
> As I understand it with Windows your windows login is used to get you into
> the SQL server. What happens after that - do you still need to assign
> users to the database?
> Thanks.
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email

Authenication Mode

I've been reading on the authentication modes - Windows and mixed (sql
server). I'd like some comments on the pros and cons of each and which
most people use.
As I understand it with Windows your windows login is used to get you into
the SQL server. What happens after that - do you still need to assign
users to the database?
Thanks.
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to emailHi,
Yes. Login will just allows you to authenticate to SQL Server. After that
you will have associate the login with a database user and
assign permission for that database user to access each of the required
databases.
Thanks
Hari
SQL Server MVP
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23yDEYNu1GHA.1256@.TK2MSFTNGP02.phx.gbl...
> I've been reading on the authentication modes - Windows and mixed (sql
> server). I'd like some comments on the pros and cons of each and which
> most people use.
> As I understand it with Windows your windows login is used to get you into
> the SQL server. What happens after that - do you still need to assign
> users to the database?
> Thanks.
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email|||Brett I.Holcomb
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:%23yDEYNu1GHA.1256@.TK2MSFTNGP02.phx.gbl...
> I've been reading on the authentication modes - Windows and mixed (sql
> server). I'd like some comments on the pros and cons of each and which
> most people use.
> As I understand it with Windows your windows login is used to get you into
> the SQL server. What happens after that - do you still need to assign
> users to the database?
> Thanks.
> --
> Brett I. Holcomb
> brettholcomb@.R777bellsouth.net
> Remove R777 to email

Wednesday, March 7, 2012

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! Chuck
My bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

audit single user mode

Any way to audit when/and by whom a DB has been changed to single user mode?
We have a couple of DB's that are being locked, and it appears to be some
process. Nothing shows up in SQL logs / event logs indicating the change.
Any way I can turn on some kind of verbose logging or set some kind of
trigger to watch for this? We think there is some process doing it, but
without a security context and/or time stamp of exactly when it happens it's
been quite difficult to track this event down. Help! ChuckMy bet is that you have a maintenance job for integrity checking that has
the 'Repair minor problems' option checked. This will put the database into
single-user mode if it finds any errors and tries to repair them. You
shouldn't run with this option on as its always better to investigate any
errors rather than blindly repairing them.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:88AAB265-3719-4D97-AD08-E61D20DC83B8@.microsoft.com...
> Any way to audit when/and by whom a DB has been changed to single user
mode?
> We have a couple of DB's that are being locked, and it appears to be some
> process. Nothing shows up in SQL logs / event logs indicating the change.
> Any way I can turn on some kind of verbose logging or set some kind of
> trigger to watch for this? We think there is some process doing it, but
> without a security context and/or time stamp of exactly when it happens
it's
> been quite difficult to track this event down. Help! Chuck

Tuesday, March 27, 2012

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

auto backup jobs wont run - not in single user mode

Thursday, March 22, 2012

Authentication mode, connecting from webpage

Authentication mode selection

Authentication Mode question from a command line junkie

Authentication Mode question from a command line junkie

Authentication Mode question from a command line junkie

Authentication Mode problems

Authentication mode / configuration issue

authentication mode

authentication mode

authentication Mode

Authentication Mode

Tuesday, March 20, 2012

Authentication

Monday, March 19, 2012

Authenication Mode

Authenication Mode

Wednesday, March 7, 2012

audit single user mode

audit single user mode

audit single user mode

Attach remote DB file

Blog Archive

About Me