Creating SQL 2005 Logins During Installation

Mar 8, 2006

Hello,

We use one standard account with a password that is never given out.

In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.

That same process does not work for SQL 2005.

Below is a sample of the script that we were deploying for SQL 2000 installations.
-------------------------------------------------------------------------------------------------------------
-- Login: CPAPP

declare @pwd varchar(50);

SET @pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @pwd, @defdb = 'CPSQL' , @sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @encryptopt = 'skip_encryption'

SET @pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @pwd,@defdb = 'CPSQL' , @sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @encryptopt = 'skip_encryption'

SET @pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @pwd, @defdb = 'CPSQL' , @sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @encryptopt = 'skip_encryption'

-------------------------------------------------------------------------------------------------------------
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

Is this what we should be using for SQL 2005?

Please advise.

Thanks in advance

View 6 Replies


ADVERTISEMENT

Creating Logins For SQL Server 2005

Jan 10, 2007

I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.

View 2 Replies View Related

SQl Server 2005 Installation Is Depend On Installation Of Visual Studio 2005?

Oct 2, 2006

Hi every one,

I'd like to install SQL SERVER 2005 Express Edition ,I begin to install it from DVD but during the installation it dosn't ask me about any instance and after installation there isn't SQLExpress service and I can't use sql server .(there are not MSSQL and Binn directory in sql server 2005) but when I install visual studio 2005 and after that I install sql server 2005 it ask me for name of instance and with this way every thing is ok and I use sql server completely.

I 'd like to know the complete installation of sql server 2005 is depend on installation of visual studio 2005 befor it ?if not how can I introduce any instance after installation of sel server 2005 .

View 11 Replies View Related

Lost SQL Logins, But Still Have DB Logins - Script To Recreate SQL Logins?

Jan 21, 2007

ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.

Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?

I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.

Any assistance would be greatly appreciated.

Thanks,

John

View 3 Replies View Related

Creating Installation Script

May 9, 2006

Hi all,

I´m newbie with SQL Server.

I need to create a installation script SQL
(creating tables, indexes, permissions, databases).
This script calls other scripts.
In Oracle I use @script_name.sql, but in SQL Server
I don´t know to do this.

Other question: Is there a way to call the Query Analyzer
in .BAT file, where the .BAT file calls installation scripts SQL.

Thank U very much,

[]´s

View 3 Replies View Related

Logins - SQL Server 2005

Sep 18, 2006

I need to create a new login that only has access to 2 Views.

I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.

View 13 Replies View Related

Sql 2005 Users Vs Logins

Feb 8, 2007

What is the relationship between the users in a database and the logins on the server.

View 1 Replies View Related

Logins From 2000 To 2005

Jun 22, 2007

As from what i see there are different system files for logins.



Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the

sp_help_revlogin i think thats the script..in 2000



Is there a way to convert all logins from SQL 2000 to SQL 2005

View 3 Replies View Related

SQL 2005 Mirroring And SQL Logins

Feb 5, 2008

Hi all,

I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!

As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).

For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.

Any help/guidance would be greatly appreciated!

Thanks,

Richard

View 3 Replies View Related

Netware Logins And How To Integrate Into SQL 2005?

Mar 17, 2008

I was wondering if anyone could help me with this. I am currently working on a project for reporting on a new acquisition the company have made. Unfortunately, the people I have to give access to the SQL Server are using Netware for authentication.

Is there any way to allow these people to log into SQL Server without having to create a SQL authenticated user for each of them?

Thanks

View 7 Replies View Related

MS SQL 2005, .NET, Logins/sec And Page Faults

Jul 25, 2006

Hi, all.We have a couple of pathological sql servers that have lots and lots ofpage faults per second, up to 4000. Our client programs are written inC#/.NET 1.1 and utilizes connection pooling.Some of the client programs seems to log in hundred of times persecond, as reported by perfmon->.SQLServer:GeneralStatistics->Logins/sec. Stopping the client programs reduces thatnumber significantly.We've done code reviews of the client programs and they look OK.Monitoring .NET connections&pools does not show anything suspicicous.We're currently rewriting the clients to use one db connection insteadof the pools, but that takes some time and may introduce bugs. Doesanyone know why we have these problems and/or why logins/sec is sohigh? I'm thinking "bugs in the .NET client", but really have noidea...One thought I had was that the Page Faults reported for sqlsrv.exe isrelated to memory mapped IO and therefore can be ignored. Right orwrong?Any thoughs/pointers/ideas, even wild guesses, are most welcome.BjørnPS: The server memory is fixed at 1.5GB out of 2GB physical ram,clients run on the same machine and use TCP/IP comm.(I know...) Thehost itself is not paging.

View 4 Replies View Related

SQL 2000 - To SQAL 2005 Logins

Aug 3, 2007

I backup SQL 2000 DB and Restored to SQL 2005

Under the Database Name, Users when i click properties the login name is blank.

When i restored from SQL 2000 to another SQL 2000 Server i would run an orphan fix

declare @usrname varchar(100), @command varchar(100)

declare Crs insensitive cursor for

select name as UserName from sys.sysusers

where issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

order by name

for read only

open Crs

fetch next from Crs into @usrname

while @@fetch_status=0

begin

select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '

exec(@command)

fetch next from Crs into @usrname

end

close Crs

deallocate Crs

I changed the sys.sysusers from sysusers.

But when i run this it does not work.

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207

An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.


Does anyone know how to fix the logins for all of the users rather than one at a time for SQL 2005

Thanks

View 9 Replies View Related

Export Logins To Server With Already Existing Logins

Jul 17, 2001

Hello,

I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.

What's the best way to solve this problem?
Please, help
Thanks

View 3 Replies View Related

Moving DBs From SQL Server Logins To Windows Logins

Apr 3, 2007

I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.

The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.

Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.

But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)

Hope this all makes sense.

View 2 Replies View Related

Transfer Logins From SQL Server 2000 To 2005

May 13, 2008

Hi

How to transfer logins from 2000 to 2005?


Raj.

View 1 Replies View Related

SQL 2005 - SQLDMO - Adding Logins/ Users

Jul 21, 2006

I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will

View 2 Replies View Related

MSSQL 2005 Security Invalid Logins

Mar 30, 2006

Right im getting slightly annoyed LOL
We have an MSSQL 2005 box in the shared hosting environment now the amount of attacks we receive every hour is a crazy amount. The usual stuff as below

Login failed for user 'sa'. [CLIENT: ]

Now i can simply block the ip's in ipsec but the amount of attacks is crazy and its taking all my time to keep track of them all, i know its just websites, automated tools etc, mssql is set to lock accounts after 4 invalid logins, well to make security sense i renamed the sa account as its default on most servers.

What i want to know is after so many attacks why can't mssql implement ipsec security on its own and block the client ip, come on after 20 attempts you know the IP is brute forcing the accoutn in question.

Any advice people?
Its a high performance server but it doesnt need brute force attacks against it all day long, LOL.

View 3 Replies View Related

Regarding Migrating Windows And SQL Logins To SQL 2005 From SQL 2000

Jul 27, 2006

Hey. I've a script which gets the SQL usernames with the information I need. Is it possible to migrate the passwords also with this information?

Also, regarding Windows Logins, do i have to worry about their SIDS when migrating to SQL 2005. I'm going to attach SQL 2000 db's onto a 2005SQL instance and these db's alreayd have the usernames assigned in them. Please let me know if there is a way out for the above two questions. thank you.

SELECT 'Create LOGIN [' + loginname + '] with password = ''hello'', sid = ',sid,
', default_database = ' + dbname + ', default_language = ' + language + ',check_expiration=off,check_policy=off'
FROM master..syslogins
WHERE name NOT IN ('sa')
AND isntname = 0

View 6 Replies View Related

What Is Maximum No. Of Concurrent Logins Possible On A SQL Server 2005 Database?

Dec 19, 2006

what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?

View 3 Replies View Related

SQL Server 2000 To 2005 Upgrade (Database Users Logins)

Jul 6, 2007

Hi All,

I have just migrated couple of test databases

from SQL Server 2000 to 2005 (side by side).



I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?



Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?



Thanks for your time..



Bidyut



View 5 Replies View Related

Creating A New Connection With Microsoft SQL Server 2005 Compact Edition From Visual Studio 2005 IDE

Jul 25, 2007



Hello, How are you?
I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?



I appreciate your help€¦

View 6 Replies View Related

A .NET Framework Error Occurred During Execution Of User-defined Routine Or Aggregate -While Creating A SQL SERVER 2005 Stored Prodecure In VS.NET 2005

Aug 15, 2007

 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)   at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is :  creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using :   Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error  ""  Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help 

View 13 Replies View Related

SQL Server 2005 Developer's Version Installation Problem -- No SQL Server 2005 Studio Manager Gets Installed.

Sep 3, 2007

I installed Visual Studio 2005 Professional then after that was installed and running, I tried to install the the SQL Server 2005 Developer's Edition which installed but I do not get the SQL Server 2005 Studio Manager. I have remove and reinstalled but it never gets installed. Any ideas?

Chuck

View 4 Replies View Related

SQL 2005 Installation

Mar 13, 2007

I do not have enough disk space on my servers C drive to install SQL 2005. How can i force the installation to place on d drive

View 3 Replies View Related

SQL 2005 SP2 (CTP) Installation

Nov 28, 2006

Hello

I am trying to install PPS (CTP) this requires SQL 2005 SP2 (CTP) but during the install of the SP2 I get the following error while trying install it:

Unexpected Error Occured

Unable to create backup Folder

Next thing is that A Send don't sent error is created for the hotfix.exe. The install terminates at this point.



I am working in a VPC image, logged on as Administrator, anyone give a hint as to what this could be



Many thanks

View 4 Replies View Related

SQL 2005 Installation

May 2, 2007

I have two Win 2003 r2 servers. These are not production servers. They are very lightweight boxes for testing apps. Everything worked find when all was on one svr01 except ... too slow.



- I installed svr02 as member of Domain svc01 ... should I make svr02 a child Domain svr01?

- Currently, I cannot add any kind of data connection via Visual Studio..



Thanks

View 1 Replies View Related

Sql 2005 Installation

Mar 16, 2008



hi
whenever i install ms sql 2005 express edition, only the configuration manager gets installed. but i need sql query analyzer. can anyyone tell me why it doesnt get installed. also plz let me the what should i do to get query analyzer installed..

View 5 Replies View Related

SQL Server 2005 Installation

Jan 28, 2007

Hi: I try to install SQL Server 2005 but it gives warning message. My computer configuration is Pentium III 900 HZ, 512 RAM with operating system windows 2000 Pro and service pack 4 and 10 GB free space. It gives warning on operating system. Also Visual Studio. Net 2005 is already installed on my comupter.

- SQL Server Edition Operating System Compatibility (Warning)
Messages
* SQL Server Edition Operating System Compatibility

* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.


- Operating System Minimum Level Requirement (Success)
Messages
* Operating System Minimum Level Requirement

* Check Passed


- Operating System Service Pack Level Requirement. (Success)
Messages
* Operating System Service Pack Level Requirement.

* Check Passed

View 1 Replies View Related

Sqlserver 2005 Installation

Jan 26, 2007

This is my first task of installing sqlserver 2005 on 64 bit machine in windows 2003 environment.
As a DBA what are the thnigs i need to know for the successful installation of sqlserver 2005 and what the things that are need to be installed along with sqlserver, and what are the accounts to be created like all the things i need to know..

Any help will be a great for me ...

Thanks in advance

View 1 Replies View Related

SQL Server 2005 Installation

Mar 1, 2007

hi,

I have to install SQL Server 2005 on SBS Standard 2003. I am already running Sql Server Desktop Engine with three instances: MSSQL$SHAREPOINT, MSSQL$SBSMONITORING, MSSQL$BLUECOAT. I am wondering how the installation is gonna affect my system. I do need SQL Server 2005 for the new financial software which is intalled 2morrow. What shall I put as the instance during the installation. Is Sql Server Desktop Engine gonna be replaced by SQL Server 2005?

help!

View 1 Replies View Related

Need A Help In Installation Of SQL Server 2005

Mar 19, 2007

i had downloaded .Net and SQL Server 2005 from microsoft site. In SQL Server 2005 after installation i get only Configuration Tools and 3 subtopics. I am able to create table only through . NET but am not able to work independantly with SQL Server 2005. Can anyone advice me!!

View 2 Replies View Related

SQL SERVER 2005 INSTALLATION

Jul 7, 2007

--------------------------------------------------------------------------------

Dear Sir,
While Installing Sql 2005 Express in IBM POS machine, the following Error comes.

The Sql Server Service has failes to Start
MESSAGE ID : 29503
Message Source : Setup.rll
evt Type : Sqlsetuplibservice.cpp@do_sqlscri


i Tried to other pos machine also , same problem Comes. But Normal Desktop pc it is running fine.

Please guide me

Thanks

View 6 Replies View Related

SQL 2005 Installation Requirements

Nov 6, 2007

Hi ,

I want to install a new SQL 2005 server. i looked on MSDN, it says to create one or more domain user accounts. That means to create a windows domain. Is it neccessary? All I want is a stand-alone SQL server. right now we are working with SQL 2000 and we want to build a new server with SQL 2005.

Please send me a short feedback.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved