Authentication to SQL Server in ASP on Active Directory

I would like to be able to autheticate users connecting to SQL Server in
Active Server Pages on MS Active Directory.
Is this possible? If so, how?
Thank you.
AAAHi,
then you have to Map AD user account / group to sql server then you are
able to do it . please refer sp_adduser for more information it can be done
very well using this sp it is used to map those Windows (AD) accounts to SQL
and then use it in you ASP connection string. What is your Authentication
mode ?!
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"AuntieAuntieAuntie" wrote:

> I would like to be able to autheticate users connecting to SQL Server in
> Active Server Pages on MS Active Directory.
> Is this possible? If so, how?
> Thank you.
> AAA|||Hi Andy,
This is the situation, (we can add users) but, not Domain Accounts that
belong to Domain Groups without a password on the connection string, within
an ASP on Active Directory, this is my current connection string:
<%
Dim strDatabaseType, objConn, cst
cst = "Provider=SQLOLEDB;data source=Z0123456;" &_
"Database=myDatabase;User ID=99;password=XXXXXXX;"
Set objConn = Server.CreateObject("ADODB.Connection")
%>
We would like to use a Domain Account as the userID without the password on
this connection string. On the database side, this account belongs to a
Domain Group, that has access to the database, it this possible? How can we
accomplish it?
AAA
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> then you have to Map AD user account / group to sql server then you are
> able to do it . please refer sp_adduser for more information it can be don
e
> very well using this sp it is used to map those Windows (AD) accounts to S
QL
> and then use it in you ASP connection string. What is your Authentication
> mode ?!
> --
> Andy Davis
> Active Crypt Team
> ---SQL Server Encryption
> Decryption Software
> http://www.activecrypt.com
>
> "AuntieAuntieAuntie" wrote:
>|||Hi,
We are using Windows Authentication for this applicaton.
AAA
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> then you have to Map AD user account / group to sql server then you are
> able to do it . please refer sp_adduser for more information it can be don
e
> very well using this sp it is used to map those Windows (AD) accounts to S
QL
> and then use it in you ASP connection string. What is your Authentication
> mode ?!
> --
> Andy Davis
> Active Crypt Team
> ---SQL Server Encryption
> Decryption Software
> http://www.activecrypt.com
>
> "AuntieAuntieAuntie" wrote:
>

Authentication question

I have a question about authentication with SQL 2005. I want to create a
group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
Would users of the AD group then be able to authenticate to the SQL Server
because the group is in SQL Server or not?You need to connect to your instance and create a login principal from the
Security for the mentioned Windows Group and map it to whatever resource
they need to reach.
To achieve this, your SQL Server server also has to be joined to the
mentioned domain.
--
Ekrem Önsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uTlojAEAIHA.536@.TK2MSFTNGP06.phx.gbl...
>I have a question about authentication with SQL 2005. I want to create a
>group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
>Would users of the AD group then be able to authenticate to the SQL Server
>because the group is in SQL Server or not?
>

Authentication Issue

We have a sql 2005 server that we need to connect to.
I am in one active directory domain (eg DomainA) and the sql 2005 server is in another (eg DomainB).

We are not allowed (by the sql 2005 server provider) to put a trust between the two active directory domains and sql server logins present an issue due to mirroring (this is not a question about mirroring).

Is there a way of logining the EnterpriseManager/QueryAnalyser applications in to the sql server 2005 in DomainB with out logging my whole machine into DomainB and with out using SQL server authentication.

Any help would be greatly apperiated

Hi,

Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Authentication issue

Hello

I'm using RS 2000 and although Windows Authentication has been selected on the website and the "Reports" virtual directory, when I browse to localhost/reports I get the report manager with only Home on it and a very limited menu bar whixh exists of home, my subcriptions and help. I can't see any folders.

What am I missing?

Thanks!

What rights does your account have in the Report Catalog. What roles does it have assigned to?|||

Thanks for answering Teo

Well, it's hard to tell as I reinstalled everything but things got even worse now.
When I browse to http://localhost/reports, I get:

Access is denied.
Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources.

Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to ACLs). Ask the Web server's administrator to give you access to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager'.

The authentication part is ok because when I deactivate windows authentication, it's just the login box that dissapears but the error above remains so I guess the problem is not authentication related but permission related.

On the virtual directory "Reports" which is 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager', I have the following permissions set up:

Administrator Full rights
Administrators Full rights
Network Service Full rights
ASP.NET Full rights
Everyone Full rights
System Full rights

what more can you do?

|||Looks like you don't have ACL rights to the files in \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager. Use the Reporting Services Configuration Tool to apply the default settings to the Report Manager Virtual Directory. This should give the necessary rights to the RS accounts.|||I'm afraid I will have to look for it myself because I'm using RS 2000. The configuration tool does not exist in 2000.|||

Are you connected to some corporate network or it is stand alone PC?

Are you in the Admin group on that machne? if not add yourself to the Admintrators group it should solve the problem.

|||I appologize I missed the RS 2000 part. Grant ASPNET (or Everyone) read access to the Report Manager folder.|||

Well, I reinstalled RS like 7 times and it's working now. Thank you all for the help!

Regards
Worf

Authentication failed error

Receiving authentication failed error on a SQL driven website.
Basic directory structure

admin
|
|
|--> inc
|
|
|-->setdefaults.inc

inc
|
|
|-->setdefaults.inc and database.mdb

The setdefaults.inc in the admin/inc folder is pointing to the database.mdb in the main inc folder. When I try to access the admin side of the website, I'm getting an authentication failed error. The public side of the website functions properly (displays all files added to the database before the error starting occuring and the search function works properly).

Our ISP migrated their servers about a month ago. Have been unable to confirm with them if the IP or path to the SQL database changed. I don't think it has because the main site still functions properly and it's the same database.

Does anyone have any suggestions on how I can troubleshoot this problem further?

Thanks for your help!

DeborahAre you using the same user accounts to log onto the database for both the admin and customer parts of the site?|||Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||You should be able to replace the Server with the IP address without any problem. Do you know what type of access your account has for the database? The user account may be limited to read only access (or somthing similar) and as a result will not allow the more advanced sections of the admin (update, insert etc).

Originally posted by vascorp
Yes - exactly the same info. The ISP finally got back to me (two months later) and gave me a host name, IP address, database name, UID and PW. The original coding done by my web designer has the fields Server Name, database name, UID and PW.

Do I just replace the Server name with the IP address and do I need to put the host name anywhere. If so, where?

Deborah|||I went line by line through my developers code, it looks like the authentication info on the public side is REMmed out so that solves the mystery as to why it appeared to be authenticating properly on one side and not on the other.

I tried replacing the server name with the IP address. It doesn't work. I just found out that the ISP provider now uses MySQL. Is that entirely different from MS SQL Server?

Thanks

Deborah|||It is a different, and as a result I imagine you will need a different connection string.

You will need somthing like

oConn.Open "Driver={mySQL};" & _
"Server=ipAddress;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

Authenticating users with Active Directory and SQL Server 2000

I've set up a web page on our network that links to an SQL Server database c
ontaining records of staff members. At the moment the web page extracts all
the rows from the table and shows all the staff records.
I want to be able to authenticate staff members using Active Directory, so w
hen they log onto their PC and access the webpage on our intranet they can O
NLY view their record from the database.
I'm just starting out with SQL Server and Active Directory, so any help woul
d be appreciated. From what I have read I think that I may need to use LDAP
to retrieve the Active Directory information.
Thanks in advance for your help.Use Windows Authentication for your web site, which will authorize the user
through AD. Then use integrated security/impersonation to pass the user
credentials to SQL for authorization and connection. Then, you can use the
USER_NAME (or similar technique) to filter the rows returned by any SQL
statement.
Hope this helps you get started...
"Enterprise Andy" <EnterpriseAndy@.discussions.microsoft.com> wrote in
message news:CBD3FEF9-ACF1-4979-9A90-4B4AF44B23C9@.microsoft.com...
> I've set up a web page on our network that links to an SQL Server database
containing records of staff members. At the moment the web page extracts all
the rows from the table and shows all the staff records.
> I want to be able to authenticate staff members using Active Directory, so
when they log onto their PC and access the webpage on our intranet they can
ONLY view their record from the database.
> I'm just starting out with SQL Server and Active Directory, so any help
would be appreciated. From what I have read I think that I may need to use
LDAP to retrieve the Active Directory information.
> Thanks in advance for your help.

Authenticating from Active Directory Domain

We use a query that gets the suser_name parameter and
inserts it into a table.
A user converted to an Active Directory Domain and the
suser_name comes up as null.
Is this the way it works? Or is there some setting that
has to be changed?
Any help appreciated.
Thanks,
GeorgeDirectly from BOL
suser_name is obsolete and always returns NULL in S2K.
"George Galcik" <anonymous@.discussions.microsoft.com> wrote in message
news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...

quote:

---

> We use a query that gets the suser_name parameter and
> inserts it into a table.
> A user converted to an Active Directory Domain and the
> suser_name comes up as null.
> Is this the way it works? Or is there some setting that
> has to be changed?
> Any help appreciated.
> Thanks,
> George
>

---

|||Thanks for your help, Scott.
George

quote:

---

>--Original Message--
>Directly from BOL
>suser_name is obsolete and always returns NULL in S2K.
>"George Galcik" <anonymous@.discussions.microsoft.com>

---

wrote in message

quote:

---

>news:005b01c3dab4$6bf0fdb0$a101280a@.phx.gbl...
>
>.
>

---

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

http://msdn2.microsoft.com/en-us/library/ms164631.aspx

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticating against LDAP/Active Directory with SQL Server 2000 SP2

Hello everyone,

We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.

So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.

Linked server 'ADSI' is set up with the sp_addlinkedserver command:

EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI', @.srvproduct=N'Active
Directory Services 2.5', @.provider=N'ADsDSOObject', @.datasrc=N'adsdatasource'

Two table functions in our test DB (trying to test both ways I've found in docs):

ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st')
WHERE [SAMAccountName] = @.userId
)

ALTER FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@.userId nvarchar(50),
@.password nvarchar(50)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT [SAMAccountName], [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT SAMAccountName, Name, SN, ST
FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' ')
WHERE [SAMAccountName] = @.userId
)

So calling either of these table functions from our custom application gives the same result:

select * from dbo.GetAuthenticatedUser('astonaker','abc')
OR
select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')

ResultSet:

'astonaker', 'Anthony', 'Stonaker' 'NULL'

So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.

I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.

Any thoughts/suggestions are greatly appreciated!

Why do you need to validate credentials on a SQL server?

If a password is entered on a client, then could you validate it there or are you going to do something as that user on a SQL server?

In that case could you impersonate a user on a client and connect to the server?

|||The app that needs this validation cannot validate against LDAP directly; it is a limited custom-built scripting language. It can easily execute queries against its remote SQL Server DB, and we are trying to investigate a solution that will allow us to run a query to validate against LDAP from there. Simply knowing whether or not the user ID/password combination entered into the app matches the Active Directory credentials is sufficient.

Edit: From browsing more formus/docs today, what do you think of using an extended stored procedure? Possibly compile the .NET connection code in a dll and plug it into sql server? From digging through more postings it looks like it might be the most direct route I'll be able to get.

Thanks for the post!
|||That extended procedure did the trick!

We ended up using IADsOpenDSObject:: OpenDSObject for this purpose. We were constrained to using C/C++ and none of the .NET classes, but there was very nice documentation in several places that served as great guides:

http://www.codeproject.com/database/extended_sp.asp
http://msdn2.microsoft.com/en-us/library/aa706065.aspx

I did see where MS noted that "This method should not be used just to validate user credentials," but the link they provided to SSPI authentication was not very helpful.

ALSO, for anyone else jumping through the same hoops, MS noted that the function srv_describe (and its companion srv_ functions) is being removed in future versions of SQL Server:

http://msdn2.microsoft.com/en-us/library/ms164631.aspx

They state to "Use CLR Integration instead," so if anyone has any links to a good reference I'd love to check them out!

Authenticate via Active Directory from PC that's not joined the do

I have several people on my team that are working remotely from PCs that are
stand-alone, ie. they have not been joined to the domain. They do have an
account on the domain, though, which they use to map drives and such. How
can I set it up so that they can use Enterprise Manager or Query Analyzer by
authenticating with their domain account?Hello,
I suggest that you create a SQL login for each user. You can refer to the
following article:
Adding a SQL Server Login
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_9m0e.asp
How to set up Mixed Mode security (Enterprise Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Make sure SQL server use mixed mode Authentication.
If the client can join the domain with a domain user account, you can add
the domain user account as a Windows login. You can refer to the following
web site:
How to grant a Windows user or group login access to SQL Server (Enterprise
Manager)
http://msdn.microsoft.com/library/d...-us/howtosql/ht
_6_secrty_68c9.asp
Granting a Windows User or Group Access to a Database
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_2wit.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Authenticate against Active Directory

We are writing an enterprise application in which the user logs in by entering their username and password in a form. The user will enter their actual Active Directory credentials. I want to pass the username and password to a stored procedure that will check for a valid user account in an Active Directory. In Sql Server 2005 I know I could write a small .NET library that uses System.DirectoryServices , register it in SQL and access it from an stored procedure.

Can it be done in Sql server 2000?

The reason we are not just autheticating in the .NET code of the application is that we found that it does not work on Windows 98 cleints, hence the need to go through sql server.

Thanks

In SQL Server 2000, you can write an extended procedure. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_9rxv.asp for additional information on extended stored procedures.

Thanks
Laurentiu

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

AttachDbName Relative Directory

I have host my ASP.NET application on gate.com, and there I have access to a SQL Server 2005 instance, and I am trying to make relative paths in my web.config work, (|Data Directory|), but I don't know how to change the default value of |Data Directory|, and it isn't the root of my application, I'm pretty sure. My setup is like so, and the file was created using SQL express 2005, I am not sure if that is my problem, because gate uses the full version.

/<root>/App_Data/Leads.mdf

here is the stack trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766
System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428
System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410
System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117
System.Configuration.SettingsBase.get_Item(String propertyName) +89
System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36
System.Web.Profile.ProfileBase.get_Item(String propertyName) +68
System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4
ProfileCommon.get_Cart() in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\27a74ff4\376615bd\App_Code.ceobor1b.4.cs:102
Default2.UpdateTotal() in \\shared.hosting.local\nfs\cust\9\80\45\754089\web\Order.aspx.cs:45
Default2.Page_Load(Object sender, EventArgs e) in \\shared.hosting.local\nfs\cust\9\80\45\754089\web\Order.aspx.cs:37
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

Hi LiquidTobi,

Could you show me your connection string for the connection?

|||Actually, I figured out a workaround, I used the aspnet_regsql tool to register the database that is hosted with gate, and I transferred my tables and views to it as well, so I no longer need to attach the mdf file.

AttachDbFilename in DbNameDataSet.xsd

How can I use AttachDbFilename in DbNameDataSet.xsd to access the database in the same directory as the exe file (i.e. the application using it)?Use Application.StartupPath to get the directory your program is in.
To get the complete filename to your mdf file, use:

string filename = System.IO.Path.Combine(Application.StartupPath, "filename.mdf");

|||Thank you but my mistake. I failed to mention it is C++ program. What you wrote appears to be VB. Tho I will add it to my VB code snippets.|||

Actually, it's C#. However, it should work with any .NET language.

AttachDatabase and Click Once

I am using Click Once to deploy SQL Express as well as the dbf files. The dbf files will be deployed to the data directory.
When my application start up I am using the Server.AttachDatabase method to attach 4 database files to the SQL Express engine. Since the dbf files gets deployed to the below folder that is read only, I am geting the error message below. How do I get around this problem? Please help.

C:\Documents and Settings\user\Local Settings\Apps\Data\5THMDK6K.YTT\KL652YA1.D7J\powe..tion_176981e083d435cc_0001.0001_d3662e0c064603fa

Error Message

C:\Documents and Settings\user\Local Settings\Apps\Data\5THMDK6K.YTT\KL652YA1.D7J\powe..tion_176981e083d435cc_0001.0001_d3662e0c064603fa\Data\\PowerAnalysisLocal.mdf" failed with the operating system error 5(Access is denied.)

Attach Code

public class DBAttach

{

private LogThis logger;

private Microsoft.SqlServer.Management.Common.ServerConnection svrConn = null;

private Server srv;

private ArrayList dbArr = new ArrayList();

public DBAttach()

{

this.logger = new LogThis("ServiceLibarary.DBAttach");

svrConn = new Microsoft.SqlServer.Management.Common.ServerConnection(@.".\SQLEXPRESS");

svrConn.Connect();

srv = new Server(svrConn);

dbArr.Add("PowerAnalysisLocal");

dbArr.Add("TimeBasedLocal");

dbArr.Add("ProgramBasedLocal");

dbArr.Add("ControlBasedLocal");

}

public void Dispose()

{

svrConn.Disconnect();

}

public void AttachFiles()

{

startLocalServer();

CommonUtils dbUtil = new CommonUtils();

string dbPath = dbUtil.getFileDirectory();

foreach (string str in dbArr)

{

if (!IsDBAttached(str))

AttachDB(str, dbPath);

}

AddSysUser();

}

public void AddSysUser()

{

StringBuilder sSQL = new StringBuilder();

// connect using window authentication

using (SqlConnection sqlConn = new SqlConnection())

try

{

sqlConn.ConnectionString = @."server=.\SQLEXPRESS;Integrated Security=SSPI;database=dbnamel;pooling=false";

sqlConn.Open();

SqlCommand comm1 = new SqlCommand("[sp_addAnalyzerUser]", sqlConn);

comm1.ExecuteScalar();

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void AttachDB(string dbName, string dbPath)

{

try

{

StringCollection strdbs = new StringCollection();

strdbs.Add(dbPath + @."\" + dbName + ".mdf");

srv.AttachDatabase(dbName, strdbs, AttachOptions.RebuildLog);

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void DetachDBS()

{

try

{

foreach (string str in dbArr)

{

if (IsDBAttached(str))

srv.DetachDatabase(str, true);

}

}

catch (Exception e)

{

logger.Error(e.ToString());

}

}

public void startLocalServer()

{

System.ServiceProcess.ServiceController serviceCtrl = new System.ServiceProcess.ServiceController();

serviceCtrl.MachineName = System.Environment.MachineName;

serviceCtrl.ServiceName = "MSSQL$SQLEXPRESS";

//Start the Sql Server windows service

try

{

//only start it if its not currently running

if (serviceCtrl.Status.ToString() != "Running")

serviceCtrl.Start();

}

catch (Win32Exception myEx)

{

logger.Error(myEx.ToString());

}

catch (Exception ex)

{

logger.Error(ex.ToString());

}

}

public bool IsDBAttached(string dbName)

{

if (srv.Databases.Contains(dbName))

return true;

else

return false;

}

}

Error 5 usually means that the service account that SQL Server is running under doesn't have read/write permissions in the folder the database is in.

You can fix this by changing the service account (via Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager | SQL Server (SQLEXPRESS) | Properties) or by granting access to the service account in file explorer.|||This needs to be done programmatically, since it is a ClickOnce installation on a client PC. The folder seems to be read only.
Thanks