Urgent, help with Authentication
The following question may be trivial, however, I just can't make it clear to myself, even after reading through the Books Online.
I have two machines in the same network.
A hosts Sql Server (namedAS),B access the databaseASD in Sql ServerAS.
A has windows logon info asUserA andPasswordA, (administrator account).
B has windows logon info asUserBandPasswordB. Application is running under this account.
The Sql Server is set toWindows and Sql Serevr Mixed Authentication Mode, The databaseASD has aUserAS andPasswordAS as the db owner.
My question is:To successfully accessAS fromB, does the connection first passA's window authentication, then pass theAS's SQL Server authentication (if set to Mixed Mode)?
If this is true,
What connection string (in ADO.Net) should I have to connect to the Database?
Or, what authentication settings I should have(set) to make a successful connection in the case describled above (B accessAS onA)?
I observered, with the following string to work: "user id=UserAS;password=PasswordASD; data source=AS;initial catalog=ASD; persist security info=True;" , I should first set the A's login credential in B by visiting B's network place.
Thanks.
For your original question, are A and B in the same domain? Also, for some background, is this SQL Server 2000 or SQL Server 2005?
If you connect using integrated security from B, where the application runs as UserB, then the connection to the server will be made using UserB's credentials, assuming that the application doesn't impersonate some other account before connecting to A.
If you want to connect using SQL Authentication, you can use sp_addlogin or CREATE LOGIN to create a SQL login, and then you can use sp_adduser or CREATE USER to add a user mapped to the login in the AS database. sp_addlogin and sp_adduser are available in SQL Server 2000. CREATE LOGIN/CREATE USER are new DDL in SQL Server 2005.
Thanks
Laurentiu
please gothrough the article Microsoft SQL Server Service Pack 3 (SP3) provides a new security enhancement related option for configuring cross-database ownership chaining, Enable cross-database ownership chaining for all databases during setup. This article discusses the cross-database ownership chaining behavior in SQL Server 2000 SP3. With this new option, you can control whether or not you permit cross-database ownership chaining. By default, this option is disabled. Microsoft recommends that you use the default option, because it makes your database server more secure.
By default, all database objects have owners. When an object such as a view, a stored procedure, or a user-defined function references another object, an ownership chain is established. For example, a table that is owned by the same user. When the same user owns the source object, the view, stored procedure, or user-defined function, and all target objects (underlying tables, views, or other objects), the ownership chain is said to be unbroken. When the ownership chain is unbroken, SQL Server checks permissions on the source object but not on the target objects.
Risks Associated with Cross-Database Ownership Chaining
Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:
| ? | Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands: exec sp_helprolemember 'db_ddladmin' exec sp_helprolemember 'db_owner' |
| ? | Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases from newly created or attached databases. |
Even though Microsoft recommends that you turn off cross-database ownership chaining for maximum security, there are some environments where you can fully trust your highly-privileged users; therefore, you can enable cross database ownership for specific databases to meet the requirements of specific applications
How to Configure Cross-Database Ownership Chaining During Setup
In Microsoft SQL Server Service Pack 3 (SP3) Setup, a new dialog box has been added to allow the system administrator to control whether or not cross database ownership chaining will be permitted. If you select Enable cross-database ownership chaining for all databases during the SQL Server 2000 SP3 setup, you are enabling this option across all databases. This was the default behavior before SQL Server 2000 SP3. Regardless of the option that you select during setup, you can later modify server and database support for cross-database ownership chaining either by using Transact-SQL commands or from SQL Server Enterprise Manager
Configuring cross-database ownership chaining by using Transact-SQL commands:
| ? | Configure cross-database ownership chaining support for the instance of SQL Server with the new Cross DB Ownership Chaining option for sp_configure. When this option is set to 0, you can control cross-database ownership chaining at the database level by using sp_dboption. When this option is set to 1, you cannot restrict cross-database ownership chaining. This is the pre-SQL Server 2000 SP3 behavior. If you change this option, include the RECONFIGURE option to reconfigure the instance without having to restart it. For example, use the following command to allow cross-database ownership chaining in all databases: EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE |
| ? | Configure cross-database ownership chaining at the database level with the new db chaining option for sp_dboption. When this option is set to false, the database cannot participate in cross-database ownership chaining as either the source or the target database. When this option is set to true, the database can participate in a cross-database ownership chain. By default, this option is false for all user databases after you apply SQL Server 2000 SP3. The following command enables cross-database ownership chaining for the Northwind database: EXEC sp_dboption 'Northwind', 'db chaining', 'true' |
