sql login permissions: February 2012

Friday, February 24, 2012

login security question

Hi,
I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will hav
e to follow.
Thank you in advance.
Sunny
Look into SQL Server Books online for "application roles" topic. This
explains about how to create and activate an application role within your
program and use it. This may require some code changes.
HTH
Prasad Koukuntla
"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
> Thank you in advance.
> Sunny
|||In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
Can you hide the "connection" username/password from the users?
"Sunanda" wrote:

> Hi,
> I have an application which connects to the SQL server. We have several users logging into this application. All of their user-id, passwords are validated and converted to an owner profile, which is then used throughout the application.
> My problem is, this owner profile should be prevented from accessing the database directly using Enterprise Manager or Query Analyser. The database should be accessible only from the application for this owner/global profile.
> How do I go about achieving this. The application was set up like this by a person long time back who is not with us anymore. Also, I do not know SQL Server Administration. So, please detail out what information I have to look up and what steps I will h
ave to follow.
> Thank you in advance.
> Sunny
|||Steve,
No the connection profile is alreay know to the users, that is why we would like to prevent users from using that in the enterprise manager to make changes.
Please let me know if there are any options.
Thanks a lot.
Sunny
"Steve Z" wrote:

> In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so actual table access is not possible. Granted, the users can still call STORED PROCEDURES from the EM and QA tools, but that is less likely to happen.
> Can you hide the "connection" username/password from the users?
> "Sunanda" wrote:
have to follow.[vbcol=seagreen]
|||"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
If you can alter the code in the client application, you can use application
roles.
1) Use Enterprise Manager to access the database / roles. New Role. click
the Application Role radio button and give it a nice secure, obscure
password.
2) Give the Application Role the appropriate permissions.
3) Revoke the users' permissions
4) in the code of the application, put in a call to a stored procedure
called (I think, from memory) sp_setAppRole (F1 for application role to see
what the stored proc is called) using the secret password for the App Role
(which you don't share with the end users).
Now your users will have the appropriate permissions when using your app,
but not when using QA or any other app.
On an entirely different tack, you can try Group Policies. Use a GP to tie
down their desktop so that they are not allowed to run Quey Analyzer or
Enterprise Manager.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
|||Bob,
Thanks for your reply. I kinda understand this Application Role approach. Could you please explain the following:
1. Say the password for the Application role is found, can a user access the database through the Query Analyser or Enterprise Manager using the application rolde/password.
2. At present the application tracks the user who makes the changes to the database through the application. The application passes the userid to the stored procedures. But if I put in a Applciation role in between, will I still have the actual userid to
track who actually did the inserts and updates through the front-end.
Thanks in advance,
Sunanda.
"Bob Simms" wrote:

> "Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> a person long time back who is not with us anymore. Also, I do not know SQL
> Server Administration. So, please detail out what information I have to look
> up and what steps I will have to follow.
> If you can alter the code in the client application, you can use application
> roles.
> 1) Use Enterprise Manager to access the database / roles. New Role. click
> the Application Role radio button and give it a nice secure, obscure
> password.
> 2) Give the Application Role the appropriate permissions.
> 3) Revoke the users' permissions
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to see
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
> On an entirely different tack, you can try Group Policies. Use a GP to tie
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>
|||<<1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>
Yes.
<<2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.>>
Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
example, a trigger to get the login name. The user name, however, will be the app role name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could you please explain
the following:
> 1. Say the password for the Application role is found, can a user access the database through the
Query Analyser or Enterprise Manager using the application rolde/password.
> 2. At present the application tracks the user who makes the changes to the database through the
application. The application passes the userid to the stored procedures. But if I put in a
Applciation role in between, will I still have the actual userid to track who actually did the
inserts and updates through the front-end.[vbcol=seagreen]
> Thanks in advance,
> Sunanda.
>
> "Bob Simms" wrote:
|||Thanks for your reply.
But can't the application role be restricted from using the QA/EM? This is not a completely secure method. Is there any alternative.
Thanks,
sunanda.
"Tibor Karaszi" wrote:

> <<1. Say the password for the Application role is found, can a user access the database through the
> Query Analyser or Enterprise Manager using the application rolde/password.>>
> Yes.
>
> <<2. At present the application tracks the user who makes the changes to the database through the
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.>>
> Yes. You can see the login id for the users, and you can use the SYSTEM_USER function in, for
> example, a trigger to get the login name. The user name, however, will be the app role name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
> news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> the following:
> Query Analyser or Enterprise Manager using the application rolde/password.
> application. The application passes the userid to the stored procedures. But if I put in a
> Applciation role in between, will I still have the actual userid to track who actually did the
> inserts and updates through the front-end.
>
>
|||> But can't the application role be restricted from using the QA/EM?
No, that is not the way it work. You need to protect the password. Why do you say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setapprole.

> Is there any alternative.
I don't know what your requirements are, as I haven't read the full thread. App roles is a nice feature for
what it is performing. Other options includes app logins using a special password (but all users will use the
same logins), app uses stored procedures and views to access data...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:CA998A64-A51E-42B4-9B8D-E06C37624B65@.microsoft.com...
> Thanks for your reply.
> But can't the application role be restricted from using the QA/EM? This is not a completely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote:

login security question

Hi,
I have an application which connects to the SQL server. We have several user
s logging into this application. All of their user-id, passwords are validat
ed and converted to an owner profile, which is then used throughout the appl
ication.
My problem is, this owner profile should be prevented from accessing the dat
abase directly using Enterprise Manager or Query Analyser. The database shou
ld be accessible only from the application for this owner/global profile.
How do I go about achieving this. The application was set up like this by a
person long time back who is not with us anymore. Also, I do not know SQL Se
rver Administration. So, please detail out what information I have to look u
p and what steps I will hav
e to follow.
Thank you in advance.
SunnyLook into SQL Server Books online for "application roles" topic. This
explains about how to create and activate an application role within your
program and use it. This may require some code changes.
HTH
Prasad Koukuntla
"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
> Thank you in advance.
> Sunny|||In my opinion, that does not seem possible. SQL doesn't know what CLIENT TO
OL is touching it. If the "connection" from the client application comes in
through a username/password, then that username/password has access to SELE
CT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so act
ual table access is not possible. Granted, the users can still call STORED
PROCEDURES from the EM and QA tools, but that is less likely to happen.
Can you hide the "connection" username/password from the users?
"Sunanda" wrote:

> Hi,
> I have an application which connects to the SQL server. We have several us
ers logging into this application. All of their user-id, passwords are valid
ated and converted to an owner profile, which is then used throughout the ap
plication.
> My problem is, this owner profile should be prevented from accessing the d
atabase directly using Enterprise Manager or Query Analyser. The database sh
ould be accessible only from the application for this owner/global profile.
> How do I go about achieving this. The application was set up like this by a person
long time back who is not with us anymore. Also, I do not know SQL Server Administr
ation. So, please detail out what information I have to look up and what steps I wil
l h
ave to follow.
> Thank you in advance.
> Sunny|||Steve,
No the connection profile is alreay know to the users, that is why we would
like to prevent users from using that in the enterprise manager to make chan
ges.
Please let me know if there are any options.
Thanks a lot.
Sunny
"Steve Z" wrote:

> In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is
touching it. If the "connection" from the client application comes in through a use
rname/password, then that username/password has access to SELECT, UPDATE, DELETE, et
c f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so a
ctual table access is not possible. Granted, the users can still call STORE
D PROCEDURES from the EM and QA tools, but that is less likely to happen.
> Can you hide the "connection" username/password from the users?
> "Sunanda" wrote:
>
have to follow.[vbcol=seagreen]|||"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
If you can alter the code in the client application, you can use application
roles.
1) Use Enterprise Manager to access the database / roles. New Role. click
the Application Role radio button and give it a nice secure, obscure
password.
2) Give the Application Role the appropriate permissions.
3) Revoke the users' permissions
4) in the code of the application, put in a call to a stored procedure
called (I think, from memory) sp_setAppRole (F1 for application role to see
what the stored proc is called) using the secret password for the App Role
(which you don't share with the end users).
Now your users will have the appropriate permissions when using your app,
but not when using QA or any other app.
On an entirely different tack, you can try Group Policies. Use a GP to tie
down their desktop so that they are not allowed to run Quey Analyzer or
Enterprise Manager.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Bob,
Thanks for your reply. I kinda understand this Application Role approach. Co
uld you please explain the following:
1. Say the password for the Application role is found, can a user access the
database through the Query Analyser or Enterprise Manager using the applica
tion rolde/password.
2. At present the application tracks the user who makes the changes to the d
atabase through the application. The application passes the userid to the st
ored procedures. But if I put in a Applciation role in between, will I still
have the actual userid to
track who actually did the inserts and updates through the front-end.
Thanks in advance,
Sunanda.
"Bob Simms" wrote:

> "Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> a person long time back who is not with us anymore. Also, I do not know SQ
L
> Server Administration. So, please detail out what information I have to lo
ok
> up and what steps I will have to follow.
> If you can alter the code in the client application, you can use applicati
on
> roles.
> 1) Use Enterprise Manager to access the database / roles. New Role. clic
k
> the Application Role radio button and give it a nice secure, obscure
> password.
> 2) Give the Application Role the appropriate permissions.
> 3) Revoke the users' permissions
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to s
ee
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
> On an entirely different tack, you can try Group Policies. Use a GP to ti
e
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>|||<<1. Say the password for the Application role is found, can a user access t
he database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>
Yes.
<<2. At present the application tracks the user who makes the changes to the
database through the
application. The application passes the userid to the stored procedures. But
if I put in a
Applciation role in between, will I still have the actual userid to track wh
o actually did the
inserts and updates through the front-end.>>
Yes. You can see the login id for the users, and you can use the SYSTEM_USER
function in, for
example, a trigger to get the login name. The user name, however, will be th
e app role name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could yo
u please explain
the following:
> 1. Say the password for the Application role is found, can a user access the datab
ase through the
Query Analyser or Enterprise Manager using the application rolde/password.
> 2. At present the application tracks the user who makes the changes to the databas
e through the
application. The application passes the userid to the stored procedures. But
if I put in a
Applciation role in between, will I still have the actual userid to track wh
o actually did the
inserts and updates through the front-end.[vbcol=seagreen]
> Thanks in advance,
> Sunanda.
>
> "Bob Simms" wrote:
>|||Thanks for your reply.
But can't the application role be restricted from using the QA/EM? This is n
ot a completely secure method. Is there any alternative.
Thanks,
sunanda.
"Tibor Karaszi" wrote:

> <<1. Say the password for the Application role is found, can a user access
the database through the
> Query Analyser or Enterprise Manager using the application rolde/password.
>>
> Yes.
>
> <<2. At present the application tracks the user who makes the changes to t
he database through the
> application. The application passes the userid to the stored procedures. B
ut if I put in a
> Applciation role in between, will I still have the actual userid to track
who actually did the
> inserts and updates through the front-end.>>
> Yes. You can see the login id for the users, and you can use the SYSTEM_US
ER function in, for
> example, a trigger to get the login name. The user name, however, will be
the app role name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
> news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> the following:
> Query Analyser or Enterprise Manager using the application rolde/password.
> application. The application passes the userid to the stored procedures. B
ut if I put in a
> Applciation role in between, will I still have the actual userid to track
who actually did the
> inserts and updates through the front-end.
>
>|||> But can't the application role be restricted from using the QA/EM?
No, that is not the way it work. You need to protect the password. Why do yo
u say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setap
prole.

> Is there any alternative.
I don't know what your requirements are, as I haven't read the full thread.
App roles is a nice feature for
what it is performing. Other options includes app logins using a special pas
sword (but all users will use the
same logins), app uses stored procedures and views to access data...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:CA998A64-A51E-42B4-9B8D-E06C37624B65@.microsoft.com...
> Thanks for your reply.
> But can't the application role be restricted from using the QA/EM? This is not a c
ompletely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote:
>

Login Security issues

RS installed on machine outside the firewall using SQL installed locally,
accessing data on SQL server inside the firewall using a shared data source.
Have given group browser rights on the top-level folder in RS.
When user initially accesses the reports home page, they are prompted for a
login and then the home page folders are displayed. Clicking on the folder
displays all the reports. Accessing a report prompts for login again. Same
user & password will display the report - and the login is not requested
again for the duration of the session.
How to avoid prompting for login twice?
Thanks
MarkI have more information on this issue...
WHen accessing from outside, the first login grants access to the home page
and the reports folders. However, when selecting a report - at the point
where we get a second login request in side the firewall - instead we get an
error:
The page cannot be displayed
Cannot find server or DNS Error|||More details...
Inside the firewall all works fine..
Outside the firewall I get these results...
This is the home page:
http://<server>/reports/Pages/Folder.aspx
This page works fine
Click on the folder for Remedy Reports and it goes here
http://<server>/Reports/Pages/Folder.aspx?ItemPath=%2fRemedy+Reports&IsDetailsView=False
This page works fine
Click on a report to view it and..
http://<server>/Reports/Pages/Report.aspx?ItemPath=%2fRemedy+Reports%2f72+Hour+Report
Outside the firewall, this page gets an error:
Cannot find server or DNS error|||Hopefully someone else can jump in with the particulars but I remember
seeing in another post
where there was a place in a config file that you need to make a change so
the server is in the form of servername.blah.blah rather than just
servername.
Just a guess since I have not had this situation occur before.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Nicks" <MarkNicks@.discussions.microsoft.com> wrote in message
news:864759C8-39B0-4D23-9F7A-914464D13D3C@.microsoft.com...
> More details...
> Inside the firewall all works fine..
> Outside the firewall I get these results...
> This is the home page:
> http://<server>/reports/Pages/Folder.aspx
> This page works fine
> Click on the folder for Remedy Reports and it goes here:
>
http://<server>/Reports/Pages/Folder.aspx?ItemPath=%2fRemedy+Reports&IsDetailsView=False
> This page works fine
> Click on a report to view it and...
>
http://<server>/Reports/Pages/Report.aspx?ItemPath=%2fRemedy+Reports%2f72+Hour+Report
> Outside the firewall, this page gets an error:
> Cannot find server or DNS error|||Bruce I did find the answer posted in the forum. I'm reposting Dmitry
Vasilevsy's response here for anyone who runs across it...
Try accessing /ReportServer virtual folder (not /Report) from outside the
intranet, if it works, you just need to configure Report Manager application
properly.
The simplest way to do this is when you are running Report Server and Report
Manager application on the same machine (this is the only option available
when you run setup). Go look at the Report Manager configuration file,
RSWebApplication.config. There is a UI section
<UI>
<ReportServerUrl>http://intranet-computer-name/ReportServer</ReportServerUrl
>
</UI>
Change it to
<UI>
<ReportServerVirtualDirectory>/ReportServer</ReportServerVirtualDirectory>
</UI>
You should be able to access Report Manager from both intranet and internet.

Login scripting to a file

I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis.

Anyone have any examples to share?

Use the following procs, you will generate the login scripts.

Cut and paste the following proc and then execute

exec sp_script_generator 'c:\scripts\', <sa pwd>.

if sql server authentication, provide the password.

Please note that, I wrote the scripts long back.

If any issues in the procs, please post the same.

if exists (select * from sysobjects where id = object_id('dbo.sp_hexadecimal') and sysstat & 0xf = 4)
drop procedure dbo.sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(255),
@.hexvalue varchar(255) OUTPUT
AS
DECLARE @.charvalue varchar(255)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH(@.binvalue)
SELECT @.hexstring = '0123456789abcdef'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue

if exists (select * from sysobjects where id = object_id('dbo.sp_displayoaerrorinfo') and sysstat & 0xf = 4)
drop procedure dbo.sp_displayoaerrorinfo
GO

CREATE PROCEDURE sp_displayoaerrorinfo
@.object int,
@.hresult int
AS
DECLARE @.output varchar(255)
DECLARE @.hrhex char(10)
DECLARE @.hr int
DECLARE @.source varchar(255)
DECLARE @.description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @.hresult, @.hrhex OUT
SELECT @.output = ' HRESULT: ' + @.hrhex
PRINT @.output
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT " sp_OAGetErrorInfo failed."
RETURN
END

set quoted_identifier off
go
if exists (select * from sysobjects where id = object_id('dbo.sp_srv_obj_scr_gen') and sysstat & 0xf = 4)
drop procedure dbo.sp_srv_obj_scr_gen
GO

/****************************************************************************************************************/
/* Procedure : sp_srv_obj_scr_gen */
/* Purpose : To generate server objects namely logins, devices and databases in a SQL Server */
/* Called From : sp_Script_Generator [Main] */
/* Author : Narasimhan Jayachandran */
/* Date : 09/28/98 */
/* Date : 03/15/00 */
/****************************************************************************************************************/

create proc sp_srv_obj_scr_gen
@.object int,
@.srvObjColl varchar(30),
@.strObjQry varchar(255),
@.strFile varchar(6000),
@.strFilePath varchar(6000)=null
as
begin

declare @.srvObjname varchar(128), @.strMethod varchar(6000),@.hr int,@.return varchar(255), @.strCmd varchar(6000)
declare @.dbCtr int, @.FileCtr int, @.DestFile varchar(6000)

select @.dbctr=0

select @.strCmd = "echo use master >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "echo go >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output

exec("declare server_obj_cursor cursor for "+ @.strObjQry)

open server_obj_cursor

fetch next from server_obj_cursor into @.srvObjname

while @.@.fetch_status = 0
begin
-- select @.srvobjname

select @.strMethod =' '+@.srvObjColl+'("'+@.srvObjname+'").Script(324,"'+@.strFile+'")'

exec @.hr = sp_OAMethod @.object, @.strMethod, @.return out

if @.hr <> 0
begin
close server_obj_cursor
deallocate server_obj_cursor
return 1
end

if @.srvObjColl="Databases" -- database files --
begin
select @.dbCtr = @.dbCtr + 1
select @.strCmd = "copy "+ @.strFile+" "+@.strFilePath+convert(varchar(30),@.dbCtr)+".sql"
end

exec master..xp_cmdshell @.strCmd, no_output

fetch next from server_obj_cursor into @.srvObjname

end

close server_obj_cursor
deallocate server_obj_cursor

if @.srvObjColl="Databases" -- database files --
begin
select @.DestFile=@.strFile
select @.strCmd = "copy ",@.FileCtr=1,@.strFile=" "
while @.FileCtr <= @.dbCtr
begin
select @.strFile = @.strFile + @.strFilePath+convert(varchar(30),@.FileCtr)+".sql + "
select @.FileCtr = @.FileCtr + 1
end
select @.strFile = left(@.strFile,len(@.strFile)-2)

select @.strCmd = @.strCmd+@.strFile+" "+@.destFile
exec master..xp_cmdshell @.strCmd, no_output

select @.strCmd = "del ",@.FileCtr=1
while @.FileCtr <= @.dbCtr
begin
select @.strCmd = "Del "+@.strFilePath+convert(varchar(30),@.FileCtr)+".sql"
exec master..xp_cmdshell @.strCmd, no_output
select @.FileCtr = @.FileCtr + 1
end
end

return 0
end

GRANT EXECUTE ON dbo.sp_srv_obj_scr_gen TO public
GO

--exec sp_script_generator "D:\backup\sqlserver\Script\"

if exists (select * from sysobjects where id = object_id('dbo.sp_script_generator') and sysstat & 0xf = 4)
drop procedure dbo.sp_script_generator
GO
/* ************************************************************************************************************** */
/* Procedure : sp_script_generator */
/* Purpose : To generate scripts for all objects of all databases in a SQL Server 6.5/7.0 */
/* Sub Procedures : sp_srv_obj_scr_gen,sp_db_obj_scr_gen,sp_tb_obj_scr_gen,sp_con_obj_scr_gen */
/* Author : Narasimhan Jayachandran */
/* Version : 1.0 */
/* Date : 09/28/98 */
/* Version : 1.1 */
/* Date : 03/25/99 */
/* Version : 2.0 */
/* Date : 02/15/00 */
/* Usage Hints : sp_Script_Generator <Output File Path>,[SA Password - optional ] */
/* Example : sp_script_generator "c:\scripts\", [SA Password] */
/* Outputs : c:\scripts\SQLSERVER_logins.sql */
/* c:\scripts\SQLSERVER_devices.sql */
/* c:\scripts\SQLSERVER_databases.sql */
/* c:\scripts\SQLSERVER_<databaseName1>.sql */
/* ... */
/* ... */
/* ... */
/* c:\scripts\<SQLSERVER>_<databaseNameN>.sql */
/* Modified Date Reason */
/* 06/25/2001 Jobs Scripts added */
/* 07/03/2001 syslogins used to pick the loginnames */
/* Windows Authentication Mode added */
/* Use the System function ServerName instead of as a parameter */
/****************************************************************************************************************/
create proc sp_script_generator
@.strFilePath varchar(6000) = null,
@.pwd varchar(128) = null

as
begin

declare @.object int, @.hr int,@.return varchar(255)
declare @.dbname varchar(128),@.ObjColl varchar(30)
declare @.strFile varchar(6000), @.strCmd varchar(6000)
declare @.strObjQry varchar(6000), @.strObjQry1 varchar(255), @.strMethod varchar(255)
declare @.dbObjColl varchar(30), @.tbObjColl varchar(30),@.srvObjColl varchar(30)
declare @.dbobjname varchar(30), @.tbobjname varchar(30),@.ObjcollName varchar(60)
declare @.srvObjFlag tinyint,@.sts tinyint,@.TbCtr int,@.ScriptType int
declare @.Ver6570 char(4),@.ProgID varchar(16), @.Property varchar(6000)
declare @.src varchar(255), @.desc varchar(255)

declare @.objJobserver int

set nocount on

if @.strFilePath is null
begin
print "Usage : sp_Script_Generator <Output File Path>, [SA Password]"
print "-"
print "Example : sp_script_generator 'c:\scripts\',[SA Password]"
print "Outputs : c:\scripts\SQLSERVER_logins.sql"
print " c:\scripts\SQLSERVER_devices.sql"
print " c:\scripts\SQLSERVER_databases.sql"
print " c:\scripts\SQLSERVER_<databaseName1>.sql"
print " .."
print " .."
print " .."
print " c:\scripts\SQLSERVER_<databaseNameN>.sql"
print "-"
return
end

-- version check
select @.Ver6570=substring(@.@.version,23,4)

select @.ProgID =
case
when @.Ver6570 = '6.50' then 'SQLOLE.SQLServer'
else 'SQLDMO.SQLServer'
-- when @.Ver6570 = '7.00' then 'SQLDMO.SQLServer'
end

-- Create an object
print "Creating SQL SERVER Object..."
exec @.hr = sp_OACreate @.ProgID, @.object out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
return
end

print "Connecting to the SQL SERVER "+@.Ver6570+"..."
-- Set the property value to true if it is Windows Authentication
if @.pwd is null
begin
-- Set a property
Print "Connecting to SQLServer using Windows Authentication Mode...!"
exec @.hr = sp_OASetProperty @.object, 'LoginSecure', TRUE
if @.hr <> 0
begin
exec sp_OAGetErrorInfo @.object, @.src out, @.desc out
select hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
return
end

-- Connect to the sql server -- Windows Authentication Mode
select @.strMethod = 'Connect("'+@.@.ServerName+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
else
begin
Print "Connecting to SQLServer using SQLServer Authentication Mode...!"
-- Connect to the sql server
select @.strMethod = 'Connect("'+@.@.ServerName+'" , "sa", "'+@.pwd+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end

-- Verify that the connection
exec @.hr = sp_OAMethod @.object, 'VerifyConnection' ,@.return out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return end

select @.srvObjFlag = 0

-- Server Objects

-- Logins
print "Creating Scripts for "+@.@.servername+" Server Logins..."
select @.strFile=@.strFilePath+@.@.servername+"_logins.sql"
select @.strCmd = "del "+@.strFile
exec master..xp_cmdshell @.strCmd, no_output

select @.srvObjColl = "Logins", @.sts = 0
select @.strObjQry =
case
when @.Ver6570 = "6.50" then
" select name from master..syslogins where status = 8 and name != 'probe' "
else
" select loginname from master..syslogins where loginname not in ('sa') "
end

exec @.sts = sp_srv_obj_scr_gen @.object,@.srvObjColl,@.strObjQry,@.strFile
if @.sts = 1 goto ProcErr

goto CloseDbCur

ProcErr: exec sp_displayoaerrorinfo @.object,@.hr
if @.srvObjFlag = 0 goto ProcFine

CloseDbCur:
close database_cursor
deallocate database_cursor
ProcFine:
exec @.hr = sp_OADestroy @.object
if @.hr <> 0
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.ObjJobServer
if @.hr <> 0
exec sp_displayoaerrorinfo @.ObjJobServer, @.hr

set nocount off
return
end

Thanks.

Naras.

|||Actually, you don't need DMO to do this - just search for sp_help_revlogin and install the procedure in your master database. It generates a script which creates all the logins on your server with the correct hash for the password in place for each login.|||

Cool.

Thanks Allen White.

But let him learn DMO...too

Thanks

Naras.

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIA
BDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIABDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIABDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Login Resolution

Friends
I have one SQL server2000 which have Mixed Mode authentication.It is working fine in PC which server instance is situating.But I have another PC which having connectvity tools of SQL Server.When I am trying to access SQL Server from the second PC through SQL authentication access getting denied.In the same time if I am accessing any of first PC's Folder using it's administrator password.Then if try with SQL authentication I am able to connect with SQL Server in the First PC from second PC .Please note SQL server instance is not in windows authentication mode .What may be the problem.Please suggest me a resolution

Thanks in Advance
Filson

What is the error logged in the SQL Server errorlog?

Thanks
Laurentiu

Login Related Question

When setting 2 servers to replicate (in the case of Push Subsription) at
what point one computer will log on to the other?
I am having some login related problems and I would like to understand the
tehnicality of that aspect
Thank you,
Samuel
Samuel,
in standard transactional replication, when the distribution agent runs,
it'll have to read commands from the distribution database and execute procs
at the subscriber.
For merge, it'll also be on synchronization/running of the merge agent.
For transactional with queued updating subscribers, the queue reader's
execution will initiate a logon.
What error are you seeing, and at what stage?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Login Rejected

Hello,I develop my application on two different workstations (one at work and one at home). On each, I created a database with the same name, and added identical users with the same login ID and password. For simplicity, let's just say the database is called "my_data", and the user is "my_user" with a password of "12345". Recently, for reasons beyond this post, I needed to copy the data from my work database to my home database. I tried both a Detach Database, and a Full Database Backup, and have gotten the following result: When I attach/restore the databases to my home computer, the login "my_user" and "12345" fails. I THINK that even though my databases have users with the same login ID and password in them, that the two users are actually DIFFERENT users because they were created on different servers. I tried to remove the user from the database, but the db server wouldn't let me since the user owned a schema (which I can't uncheck in user preferences because it is grayed out). So I think I need to figure out how to remove the user that is in the restored database, and re-add the user that exists on the server that the database was restored to. Any suggestions? How do I remove the owned schema from the user that is in the database? Thanks! Mike

Hi,

when you use a SQL Database User (not an active directory user), the SQL Server generates SID's for the users. In your case the SQL Server generates two different SID's for the user. You can try to use the following stored procedure to synchronise the SID's:

- sp_change_users_login 'report' -> Displays all missing users
- sp_change_users_login 'Auto_Fix', 'HERE_USER_NAME', NULL, ''

See the msdn site (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) for more information.

Regards
Marc André

Login question

I have a login that I need to use across multiple servers in a thin
client application. It's for internal use so one login will suffice. I
initially thought I could just make a login on our domain and then add
this user to each server as a db owner of the respective databases.
However, I can't use the login in a connection string (as far as I
know) as I can't pass down the username/password as connection strings
can't take domain logins.
Is there a way around this? Or should I just create the user on each of
the multiple SQL servers, and then if the password changes sync it
accross manually?> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
When you use Windows logins, the login is validated by Windows, you don't pass this in the
connection string, all you say is to ask for a Trusted or a Windows login.
But I'm sure you don't want all end-users to share the same account in Windows...? So, create a SQL
Server login instead.
Or you might want to read about "application roles", which allow each user to have its own login,
but still have one user (the application role) in the database. This way, you don't lose
traceability regarding who did what.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sloppycode@.gmail.com> wrote in message
news:1145437475.402577.130300@.i40g2000cwc.googlegroups.com...
>I have a login that I need to use across multiple servers in a thin
> client application. It's for internal use so one login will suffice. I
> initially thought I could just make a login on our domain and then add
> this user to each server as a db owner of the respective databases.
> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
> Is there a way around this? Or should I just create the user on each of
> the multiple SQL servers, and then if the password changes sync it
> accross manually?
>

Login Question

Hi
Current configuration: SQL Server with SQL Security.
A disc where the backup file is written is protected and ony the members of
the domain are allowed to write to it.
Problem when a user sends a request to the SQL Server for a backup some sort
of errors occurs because the database doesn't have the permission to write
to the disc
I would expect that although the database does the backup the permission to
write to the disc will be based on the user that sent the request.
Can anyone please clarify the situation
Thank you in advance,
Shmuel Shulman
SBS Technologies LTDHi,
No, that depends up the SQL Server service startup account.
Thanks
Hari
SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:ugJEuqtlFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi
> Current configuration: SQL Server with SQL Security.
> A disc where the backup file is written is protected and ony the members
> of the domain are allowed to write to it.
> Problem when a user sends a request to the SQL Server for a backup some
> sort of errors occurs because the database doesn't have the permission to
> write to the disc
> I would expect that although the database does the backup the permission
> to write to the disc will be based on the user that sent the request.
> Can anyone please clarify the situation
> Thank you in advance,
> Shmuel Shulman
> SBS Technologies LTD
>|||Thnks,
Shmuel
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eoioCEulFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Hi,
> No, that depends up the SQL Server service startup account.
> Thanks
> Hari
> SQL Server MVP
> "S Shulman" <smshulman@.hotmail.com> wrote in message
> news:ugJEuqtlFHA.3256@.TK2MSFTNGP12.phx.gbl...
>

Login question

I have a login that I need to use across multiple servers in a thin
client application. It's for internal use so one login will suffice. I
initially thought I could just make a login on our domain and then add
this user to each server as a db owner of the respective databases.
However, I can't use the login in a connection string (as far as I
know) as I can't pass down the username/password as connection strings
can't take domain logins.
Is there a way around this? Or should I just create the user on each of
the multiple SQL servers, and then if the password changes sync it
accross manually?> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
When you use Windows logins, the login is validated by Windows, you don't pa
ss this in the
connection string, all you say is to ask for a Trusted or a Windows login.
But I'm sure you don't want all end-users to share the same account in Windo
ws...? So, create a SQL
Server login instead.
Or you might want to read about "application roles", which allow each user t
o have its own login,
but still have one user (the application role) in the database. This way, yo
u don't lose
traceability regarding who did what.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sloppycode@.gmail.com> wrote in message
news:1145437475.402577.130300@.i40g2000cwc.googlegroups.com...
>I have a login that I need to use across multiple servers in a thin
> client application. It's for internal use so one login will suffice. I
> initially thought I could just make a login on our domain and then add
> this user to each server as a db owner of the respective databases.
> However, I can't use the login in a connection string (as far as I
> know) as I can't pass down the username/password as connection strings
> can't take domain logins.
> Is there a way around this? Or should I just create the user on each of
> the multiple SQL servers, and then if the password changes sync it
> accross manually?
>

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.

Login prompt when deploying solution/report to report server

Hi there,

I've seen some similar posts to this one, however none with an answer and I'm wondering if anyone has actually figured out what causes this issue. I'm running SQL Server 2005 Reporting Services.

I'm attempting to implement a folder structure for the report server, therefore am creating individual solutions in Visual Studio for each folder. At the top level i.e http://servername/reportserver/ I am able to deploy reports no problem.

However creating a folder at the next level in the tree and then deploying to that level such as http://servername/reportserver/ManagementReports causes a Reporting Services Login prompt to appear. No matter what user credentials I enter they are not accepted. I have full administrator rights the server, the site and the folders in question.

Has anyone else experienced this? Does anyone have any suggestions?

Thanks

Matt

You need to split your deployment path. Remove the folder name from TargetServerURL and add it to TargetReportFolder.

If you want to have a new report projoect that gets deployed to a folder under your ManagementReports folder, you change TargetReportFolder to be ManagementReports/Name of new folder.

Kaisa

|||

Thanks very much Kaisa, that has worked. I should have spotted that

Login Prompt after ~3min timeout

I have a report which I am running from a web front end, which, when exported
to excel with certain parameters, takes several minutes to run. After
something between 2.5mins and 3mins this will prompt for a username and
password. Providing a valid username and passoword gives you another 3mins
before you get prompted again and so on until you have been prompted 3 times,
after which you get an IIS permission denied message up.
Running a SQL Server trace against the server makes it appear as if the
report restarts running as the stored procedure is re-run (even though this
had completed successfully before the timeout was hit), so the report appears
to start again.
Looking at the Logfiles there are message about orphaned requests and Item
with session ... not found in database.
I have tried increasing the:
Database query timeout to 240 secs - no effect.
The report timeout to unlimited - no effect.
MaxMemoryLimit to 80% - no effect.
ASP timeout to 600 sec - no effect.
I can't see any error messages in the event log.
Does anybody have any idea what is actually timing out, and even where I can
set it.
Please through in random suggestions, I am willing to try anything. BTW
this is happening on both the server which is running SP2 and the one with no
service packs on.
Many thanksI should have added:
It is nothing to do with permissions as this is fine with a smaller dataset,
also I am administrator on, one of the machines on which this problem is
occuring.
Session timeout values are default of 20mins.
"Nicola Jones" wrote:
> I have a report which I am running from a web front end, which, when exported
> to excel with certain parameters, takes several minutes to run. After
> something between 2.5mins and 3mins this will prompt for a username and
> password. Providing a valid username and passoword gives you another 3mins
> before you get prompted again and so on until you have been prompted 3 times,
> after which you get an IIS permission denied message up.
> Running a SQL Server trace against the server makes it appear as if the
> report restarts running as the stored procedure is re-run (even though this
> had completed successfully before the timeout was hit), so the report appears
> to start again.
> Looking at the Logfiles there are message about orphaned requests and Item
> with session ... not found in database.
> I have tried increasing the:
> Database query timeout to 240 secs - no effect.
> The report timeout to unlimited - no effect.
> MaxMemoryLimit to 80% - no effect.
> ASP timeout to 600 sec - no effect.
> I can't see any error messages in the event log.
> Does anybody have any idea what is actually timing out, and even where I can
> set it.
> Please through in random suggestions, I am willing to try anything. BTW
> this is happening on both the server which is running SP2 and the one with no
> service packs on.
> Many thanks|||The problem was caused by the ConnectionTimeout value in machine.config.
This was set to 2mins.
"Nicola Jones" wrote:
> I should have added:
> It is nothing to do with permissions as this is fine with a smaller dataset,
> also I am administrator on, one of the machines on which this problem is
> occuring.
> Session timeout values are default of 20mins.
> "Nicola Jones" wrote:
> > I have a report which I am running from a web front end, which, when exported
> > to excel with certain parameters, takes several minutes to run. After
> > something between 2.5mins and 3mins this will prompt for a username and
> > password. Providing a valid username and passoword gives you another 3mins
> > before you get prompted again and so on until you have been prompted 3 times,
> > after which you get an IIS permission denied message up.
> >
> > Running a SQL Server trace against the server makes it appear as if the
> > report restarts running as the stored procedure is re-run (even though this
> > had completed successfully before the timeout was hit), so the report appears
> > to start again.
> >
> > Looking at the Logfiles there are message about orphaned requests and Item
> > with session ... not found in database.
> >
> > I have tried increasing the:
> >
> > Database query timeout to 240 secs - no effect.
> > The report timeout to unlimited - no effect.
> > MaxMemoryLimit to 80% - no effect.
> > ASP timeout to 600 sec - no effect.
> >
> > I can't see any error messages in the event log.
> >
> > Does anybody have any idea what is actually timing out, and even where I can
> > set it.
> >
> > Please through in random suggestions, I am willing to try anything. BTW
> > this is happening on both the server which is running SP2 and the one with no
> > service packs on.
> >
> > Many thanks|||Sorry that should have said metabase.xml not machine.config.
"Nicola Jones" wrote:
> The problem was caused by the ConnectionTimeout value in machine.config.
> This was set to 2mins.
> "Nicola Jones" wrote:
> > I should have added:
> >
> > It is nothing to do with permissions as this is fine with a smaller dataset,
> > also I am administrator on, one of the machines on which this problem is
> > occuring.
> >
> > Session timeout values are default of 20mins.
> >
> > "Nicola Jones" wrote:
> >
> > > I have a report which I am running from a web front end, which, when exported
> > > to excel with certain parameters, takes several minutes to run. After
> > > something between 2.5mins and 3mins this will prompt for a username and
> > > password. Providing a valid username and passoword gives you another 3mins
> > > before you get prompted again and so on until you have been prompted 3 times,
> > > after which you get an IIS permission denied message up.
> > >
> > > Running a SQL Server trace against the server makes it appear as if the
> > > report restarts running as the stored procedure is re-run (even though this
> > > had completed successfully before the timeout was hit), so the report appears
> > > to start again.
> > >
> > > Looking at the Logfiles there are message about orphaned requests and Item
> > > with session ... not found in database.
> > >
> > > I have tried increasing the:
> > >
> > > Database query timeout to 240 secs - no effect.
> > > The report timeout to unlimited - no effect.
> > > MaxMemoryLimit to 80% - no effect.
> > > ASP timeout to 600 sec - no effect.
> > >
> > > I can't see any error messages in the event log.
> > >
> > > Does anybody have any idea what is actually timing out, and even where I can
> > > set it.
> > >
> > > Please through in random suggestions, I am willing to try anything. BTW
> > > this is happening on both the server which is running SP2 and the one with no
> > > service packs on.
> > >
> > > Many thanks

Login process error

Hi guys
Just setup a new system running Win2k3, SQL Server 2005, etc (all msdn).
I've got a web app (.net 2) which is running perfectly well, most of the
time, and then there's one page where I'm getting the following error...
Server Error in '/' Application.
A connection was successfully established with the server, but then an error
occured during the login process. (provider: Shared Memory Provider, error:
0
- No process is on the other end of the pipe.)
I guess the question is what does this message mean and how can I fix it?!
It's a website that's working fine apart from that page, and as far as I can
tell the code should be fine. Help!!
Cheers
Danmusosdev wrote:
> Hi guys
> Just setup a new system running Win2k3, SQL Server 2005, etc (all
> msdn). I've got a web app (.net 2) which is running perfectly well,
> most of the time, and then there's one page where I'm getting the
> following error...
> Server Error in '/' Application.
> A connection was successfully established with the server, but then
> an error occured during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> I guess the question is what does this message mean and how can I fix
> it?! It's a website that's working fine apart from that page, and as
> far as I can tell the code should be fine. Help!!
> Cheers
>
> Dan
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
http://blogs.msdn.com/sql_protocols.../22/483684.aspx (see
message 4)
David Gugick - SQL Server MVP
Quest Software

login probs after restore

sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Chris,
the SID in master.dbo.sysxlogins needs to map to the SID
in yourdb.dbo.sysusers and there is a sp to help you remap
them in a backup/restore scenario:
sp_change_users_login
Full details are in BOL, but this procedure will mean you
can retain the users and permissions.
HTH,
Paul Ibison|||Thanks Paul. Do you know what I use for the WINNT guys?
>--Original Message--
>Chris,
>the SID in master.dbo.sysxlogins needs to map to the SID
>in yourdb.dbo.sysusers and there is a sp to help you
remap
>them in a backup/restore scenario:
>sp_change_users_login
>Full details are in BOL, but this procedure will mean you
>can retain the users and permissions.
>HTH,
>Paul Ibison
>.
>|||Chris,
as far as I understand these should be OK as the SID won't
change as you're still in the same domain. You can use
sp_validatelogins to be sure.
Also, Rand from MS has previously posted up this link for
mapping both types of logins :
http://support.microsoft.com/default.aspx?kbid=298897
Regards,
Paul Ibison|||Also, search KB and read about sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR

login probs after restore

Login problems with SQL Express edition.

Hi,

when running my application and trying to access an SqlServer express edition DB, I encounter the folowing error:

Cannot open user default database. Login failed.

Does any one know how this problem can be resolved?

It is the SQL Server that comes togather with the VS2005.

Thank you!

The problem doesnt occure when i run the application using the default web server (cassini). It only occures when I run it using IIS.

But how can i run the application on IIS and use the DB?

Thnaks.

Login problems when rendering reports

This is a multi-part message in MIME format.
--=_NextPart_000_008D_01C4C8B6.1764EDB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am continually getting the following error when attempting to render = my reports from http://hostname/ReportServer...
An error has occurred during report processing. (rsProcessingAborted) = Get Online Help a.. Cannot create a connection to data source 'DataCollection'. = (rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a = trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource = (integrated, NO Credentials, SQL authentican, etc.), and it still = doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on = Server B:
3. Created a user called ReportUser on BOTH the Reporting Services = server as well as the Datasource. I made sure the user has read access = in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource = connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_008D_01C4C8B6.1764EDB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am continually getting the following = error when attempting to render my reports from
http://hostname/ReportServer...= .

An error has occurred during report = processing. (rsProcessingAborted) Get Online = Help
Cannot create a connection to data = source 'DataCollection'. (rsErrorOpeningConnection) Get = Online Help
Login failed for user '(null)'. = Reason: Not associated with a trusted SQL Server connection. =

I have tried EVERY possible security = setting for the DataSource (integrated, NO Credentials, SQL authentican, etc.), and = it still doesn't work.
Here is how it's set up:

1. Reporting Services metadata location = on Server A:
2. Datasource (from which I am pulling = data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as = well as the Datasource. I made sure the user has read access in the dbs on = both servers (including adding to the the RSExecRole).
4. I have designed my reports using = ReportUser as the DataSource connection.

Books online is not helping me = troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_008D_01C4C8B6.1764EDB0--This is a multi-part message in MIME format.
--=_NextPart_000_00AD_01C4C8BB.E4981C30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Okay: I finally got it to work by configuring my datasource to use ="Shared DataSource".
And then: Configuring my datasource (in ReportDesigner) to use a SQL =Authenticated login...
The security model in ReportServices is a bit tough to navigate in my =opinion.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:eMhumkPyEHA.2764@.TK2MSFTNGP10.phx.gbl...
I am continually getting the following error when attempting to render =my reports from http://hostname/ReportServer...
An error has occurred during report processing. (rsProcessingAborted) =Get Online Help a.. Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a =trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource =(integrated, NO Credentials, SQL authentican, etc.), and it still =doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on =Server B:
3. Created a user called ReportUser on BOTH the Reporting Services =server as well as the Datasource. I made sure the user has read access =in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource =connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_00AD_01C4C8BB.E4981C30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Okay: I finally got it to work by =configuring my datasource to use "Shared DataSource".
And then: Configuring my =datasource (in ReportDesigner) to use a SQL Authenticated login...
The security model in ReportServices is =a bit tough to navigate in my opinion.
"Dan Carollo (hotmail)" =wrote in message news:eMhumkPyEHA.2764=@.TK2MSFTNGP10.phx.gbl...
I am continually getting the =following error when attempting to render my reports from
http://hostname/ReportServer">http://hostname/ReportServer...=.

An error has occurred during report =processing. (rsProcessingAborted) Get =Online Help
Cannot create a connection to data =source 'DataCollection'. (rsErrorOpeningConnection) Get =Online Help
Login failed for user '(null)'. =Reason: Not associated with a trusted SQL Server connection. =
I have tried EVERY possible security =setting for the DataSource (integrated, NO Credentials, SQL authentican, etc.), =and it still doesn't work.
Here is how it's set up:

1. Reporting Services metadata =location on Server A:
2. Datasource (from which I am =pulling data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as =well as the Datasource. I made sure the user has read access in the dbs =on both servers (including adding to the the RSExecRole).
4. I have designed my reports using =ReportUser as the DataSource connection.

Books online is not helping me =troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_00AD_01C4C8BB.E4981C30--|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C4CB2B.5BA97140
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Well: This is odd...
I came back into the office on Monday morning -- attempted to render =the same reports and I'm back to the same error again:
Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with a =trusted SQL Server connection.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:#Ba5z7PyEHA.3844@.TK2MSFTNGP12.phx.gbl...
Okay: I finally got it to work by configuring my datasource to use ="Shared DataSource".
And then: Configuring my datasource (in ReportDesigner) to use a SQL =Authenticated login...
The security model in ReportServices is a bit tough to navigate in my =opinion.
"Dan Carollo (hotmail)" <dcarollo@.hotmail.com> wrote in message =news:eMhumkPyEHA.2764@.TK2MSFTNGP10.phx.gbl...
I am continually getting the following error when attempting to =render my reports from http://hostname/ReportServer...
An error has occurred during report processing. =(rsProcessingAborted) Get Online Help a.. Cannot create a connection to data source 'DataCollection'. =(rsErrorOpeningConnection) Get Online Help a.. Login failed for user '(null)'. Reason: Not associated with =a trusted SQL Server connection.
I have tried EVERY possible security setting for the DataSource =(integrated, NO Credentials, SQL authentican, etc.), and it still =doesn't work.
Here is how it's set up:
1. Reporting Services metadata location on Server A:
2. Datasource (from which I am pulling data to render reports) is on =Server B:
3. Created a user called ReportUser on BOTH the Reporting Services =server as well as the Datasource. I made sure the user has read access =in the dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using ReportUser as the DataSource =connection.
Books online is not helping me troubleshoot this error.
Any ideas?
Thanks,
Dan
--=_NextPart_000_002C_01C4CB2B.5BA97140
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Well: This is =odd...
I came back into the office on Monday =morning -- attempted to render the same reports and I'm back to the same error again:
Cannot =create a connection to data source 'DataCollection'. (rsErrorOpeningConnection) =Get Online =Help
Login failed for user '(null)'. =Reason: Not associated with a trusted SQL Server connection.
"Dan Carollo (hotmail)" =wrote in message news:#Ba5z7PyEHA.3844=@.TK2MSFTNGP12.phx.gbl...
Okay: I finally got it to work =by configuring my datasource to use "Shared DataSource".
And then: Configuring my =datasource (in ReportDesigner) to use a SQL Authenticated login...

The security model in ReportServices =is a bit tough to navigate in my opinion.

"Dan Carollo (hotmail)" =wrote in message news:eMhumkPyEHA.2764=@.TK2MSFTNGP10.phx.gbl...
I am continually getting the =following error when attempting to render my reports from
http://hostname/ReportServer">http://hostname/ReportServer...=.

An error has occurred during =report processing. (rsProcessingAborted) Get =Online Help
Cannot create a connection to =data source 'DataCollection'. (rsErrorOpeningConnection) Get =Online Help
Login failed for user ='(null)'. Reason: Not associated with a trusted SQL Server connection.

I have tried EVERY possible =security setting for the DataSource (integrated, NO Credentials, SQL authentican, =etc.), and it still doesn't work.
Here is how it's set =up:

1. Reporting Services metadata =location on Server A:
2. Datasource (from which I am =pulling data to render reports) is on Server B:
3. Created a user called ReportUser on BOTH the Reporting Services server as =well as the Datasource. I made sure the user has read access in the =dbs on both servers (including adding to the the RSExecRole).
4. I have designed my reports using =ReportUser as the DataSource connection.

Books online is not helping me =troubleshoot this error.

Any ideas?

Thanks,
Dan

--=_NextPart_000_002C_01C4CB2B.5BA97140--

Login Problems using .net 2 Login Control

Hi There,

I have a really strange problem. I am creating a website with an administration back end. We have a seperate sql 2000 server which we are using hold authentication credientials (after having used the aspnet_regsql.exe to setup the appropriate tables.

When testing the application on the local machine, everything works fine. The web admin works, I can login, I can retrieve data to display on the rest of the site. Once uploaded to the live environment things change.

I can still connect to the sql server i.e. I have information being displayed on the public area of the site which is coming from the sql 2000 db. I can type in the incorrect login details to the admin section of the site and the system will return an error, which is correct as well, however, if I type in the correct login information, the page hangs for awhile and then displays this error: My question is, why is it doing this when it works find on my local box and the DB connection is actually working on other parts of the site? Can anyone help?

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)

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: 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)

Make sure TCP/IP activated as a connection method to the sql server and firewall doesn't block the connection.

|||

HI.

There is no firewall between the two servers and TCP/IP is enabled. The fact is, a connection is working elsewhere on the site, but the login just doesnt work.

|||How do you know the connection is working? Do you mean the conneciton can be established to the remote SQL 2000 instance? You can turn on Audit Login for the SQL 2000 instance (right click the instance in Enterprise Manager->check Properties->switch to Security tab->change the Audit Level to All->restart server, then if there is any login failure, you can find the login failure error message in SQL ERRORLOGs.|||

I think I have figured out what the problem is. I had the membership data source defined but not the roles, this meant that the roles were being stored in the sqlexpress DB while the membership details were being stored on SQL 2000. On my local machine, it could see both DB but when uploaded to the servers (that do not support sqlexpress DB) it was failing to connect once a successful login was taking place and roles needed to be allocated.

Pity the error reported gave no indication of what the problem really was.

Now I am having issues with creating the necessary permissions tables on the server. Once I get it all going and tested, I will clost this post as being solved.

Login problems on Vista before and after SP2

Hi:

I have recently updated my Sony Vaio and installed Windows Vista. After installing I could not access my SQL databases because of the NT Authorisation / sysadmin problem.

I have since installed SQLServer SP2 (Nov CTP) but the problem persists. If I run SQL Management Studio under the administrative account I can login just fine. I have also made sure the 'NT AUTHORITY\SYSTEM' is marked for sysadmin access (which it is).

The eror I get is Error 18456.

What else can I do to gain access via Windows Authorisation?

TIA,

MartinH.

Hello Martin,

Are you are logging into the Vista under any domain credentials? If so please add the domain user as the local system administrator and try.

Thanks,

Prakash P [MSFT]

|||

Prakash:

Sorry to be so dense, but I'm not quite sure what you are asking.

I am using Vista on a portable that does not connect to any Domain Server. The locally created user belongs to the Administrators group.

Is this what you meant by 'please add the domain user as the local system administrator'?

Thanks for your reply.

Martin.

|||

Here is a workaround for resolving this issue:

1. Run SSMS “As an Administrator”, by right clicking the SSMS icon and clicking “Run As Administrator” option and select Windows Authentication and login to SQL Server.

2. In the Object Explorer node select the Security node and then select Logins node. Add a new login for your windows account and select Windows Authentication. Also go the Server Roles pane in the same window and select sysadmin role for it.

3. Close SSMS and start it normally and give your domain account and select Windows Authentication and you will not have any issues thereafter.

|||

Rajesh:

That did the trick!!

Thanks very much for your help.

Regards,

Martin.

Login problems after Restore

I restored a DB from one SQL server to the other. Before doing the Restore I created a blank DB on the destination server. The destination server already had all the logins that were present on the source server. I also created the same users in the destination DB as the source. Before doing the Restore if I look at the users in the Enterprise manager I can see all the users that I created. However after the Restore the only user I see in the Enterprise manager is 'dbo'! If I login using isql and go the newly restored DB and issue 'sp_helpuser', it shows me all the users that I created.

So why don't I see these users from Enterprise manager?? It looks like an ID mismatch problem between syslogins and sysusers.

Next I login using ISQL to the destination server using one of the corrupted? logins. I try to go to the restored DB and it complains that this is not a valid user. So I come out and login in again as SA and go the restored database and try to add the earlier user. It says user already exists. :mad: I'm not sure whats the right way out here. :confused: Any help appreciated.

ThanksIssue sp_change_users_login 'report' which will display all unmapped SQL Server Standard Security-based users that need to be fixed. You can either issue sp_change_users_login 'Update_One', <userid>, <login>, or generate a script with the above syntax.

Login problems after publishing (sql express server)

Thanks for taking your time to read this.(sorry if someone posted this earlier but no solutions helped)

After i published my website, i am unable to login anymore. It has this error of "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

Note ** im using vb

This may cuased by the IIS account (5.0 is ASPNET and 6.0 is NT AUTHORITY\NETWORK SERVICE) used to connect to SQL is not allowed to generate the user instance. I'm sorry to say that I don't know how to give the permission to the account, maybe you can try to add the IIS account to local machine Administrators group. But I always use a workaround: remove the User Instance attribute from your connection string as I do not need it. To know more about connection string, you can visit this link:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(d=ide).aspx

login problems after database move

I'm trying to move a darabase from one sql2005 server to another sql2005 server within the same domain.

I have tried both backup/restore and attach/dettach and get the same problem.

With this database I'm using a SQL Server user, the user name is on both servers with exactly the same password.

On the new sql 2005 server after I have moved the database to it, if I goto security->Users and do properties on the username. It says the user is set to "Without login" how do I change this to "Login name" ?

thanks,

martin

Hi there,

I believe, from your description, that you haven't resolved the user logins after restoring your database backup to the new server.

You can read more about this in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;240872

You can fix your problem by following the instructions in the article but I'm getting the impression from your post you only want to fix a single user (which is a SQL Server account & not a Windows domain account) so it might be quicker to use the "sp_change_users_login" stored procedure.

You can find more info on the stored proc in Books Online, but here's a link to it on the web:
http://msdn2.microsoft.com/en-us/library/ms174378.aspx

I believe that the syntax you have to use is:

EXEC sp_change_users_login @.Action = 'Update-One', @.userNamePattern = '<Database User Name>', @.login = '<SQL Server Login>'

Hope that helps a bit but sorry if it doesn't
|||

Thanks problem solved.

martin

Friday, February 24, 2012

login security question

login security question

Login Security issues

Login scripting to a file

Login Script

Login Script

Login Script

Login Resolution

Login Related Question

Login Rejected

Login question

Login Question

Login question

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

Login prompt when deploying solution/report to report server

Login Prompt after ~3min timeout

Login process error

login probs after restore

login probs after restore

login probs after restore

Login problems with SQL Express edition.

Login problems when rendering reports

Login Problems using .net 2 Login Control

Login problems on Vista before and after SP2

Login problems after Restore

Login problems after publishing (sql express server)

login problems after database move

sql login permissions

Blog Archive

About Me