Impact of migration on Windows authenticated SQL user account

Yes, I am trying to migrate a few SQL servers from NT4
domain to the new AD domain. These SQL servers have many
Windows authenticated SQL user account. As I understand,
once the logins have been defined as <NT4
domain>\<username>, we cant change it to <AD
domain>\<username>, unless we delete the logins and
recreate it again?
The article (http://support.microsoft.com/default.aspx?
scid=kb;EN-US;240872) seems to mention the moving of
database from one server to another. However in my case,
the database will remain in the same server, just that
the domain will change...hence affecting all the
previously created Windows authenticated SQL user
accounts.
Is there any step-by-step document on how to do security
translation for these SQL user accounts after the
migration to the new domain?

>--Original Message--
>Message unavailableSomething like this ... it's been awhile ... SQL Server maps SIDS to SQL Se
rver id's. There's a technique/procedure that help to remap these when like
your self domain SIDS don't match up with SQL Server id's. Sorry, but I do
nt' do it often enough to r
emember exactly.

Impact of AD Migration on Windows authenticated SQL user account

We have SQL applications in which some of the SQL user
accounts are integrated with Windows authentication.
We would like to find out whether any of you have
migrated any SQL based applications of which the SQL user
accounts were configured to make use of Windows
authentication instead of SQL authentication. Will the
Windows accounts in SQL be automatically translated to
the new AD account (with all necessary SQL
rights/permissions) or do we have to manually recreate &
reconfigure all Windows accounts in SQL and its
rights/permissions?
Appreciate some feedback. Thanks.
.Hi
If you are using the AD migration tool then you should keep the same SIDs
and therefore there should not be a problem.
If you do change SIDS then the amount of rework would be reduced if you used
NT groups when granting premissions rather than individual users.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c11b01c40893$25655870$a301280a@.phx.gbl...
> We have SQL applications in which some of the SQL user
> accounts are integrated with Windows authentication.
> We would like to find out whether any of you have
> migrated any SQL based applications of which the SQL user
> accounts were configured to make use of Windows
> authentication instead of SQL authentication. Will the
> Windows accounts in SQL be automatically translated to
> the new AD account (with all necessary SQL
> rights/permissions) or do we have to manually recreate &
> reconfigure all Windows accounts in SQL and its
> rights/permissions?
> Appreciate some feedback. Thanks.
> .
>|||Personally I doubt that ADMT does translate any SQL
permissions and rights assigned to Windows-authenticated
SQL user accounts.
Besides, I realized that from the Security, Logins folder
that once the logins are defined as <NT4
domain>\<username>, we cant changed it, even before the
migration of the SQL server computer account.
Any more feedback?

>--Original Message--
>Hi
>If you are using the AD migration tool then you should
keep the same SIDs
>and therefore there should not be a problem.
>If you do change SIDS then the amount of rework would be
reduced if you used
>NT groups when granting premissions rather than
individual users.
>John
>"Teo Chee Yang" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c11b01c40893$25655870$a301280a@.phx.gbl...
user
&
>
>.
>|||Hi
This sounds like you are changing the domain as well during the migration.
In which case look at:
http://support.microsoft.com/defaul...kb;EN-US;240872
I believe in the documentation that SIDs are retained by ADMT, if it did not
all sorts of other privilege related problems would occur.
John
"Teo Chee Yang" <anonymous@.discussions.microsoft.com> wrote in message
news:c64d01c40915$7d155610$a401280a@.phx.gbl...
> Personally I doubt that ADMT does translate any SQL
> permissions and rights assigned to Windows-authenticated
> SQL user accounts.
> Besides, I realized that from the Security, Logins folder
> that once the logins are defined as <NT4
> domain>\<username>, we cant changed it, even before the
> migration of the SQL server computer account.
> Any more feedback?
>
> keep the same SIDs
> reduced if you used
> individual users.
> wrote in message
> user
> &

Imitating ownership possible?

In SQL Server 2000 you could use the SETUSER command
(http://msdn2.microsoft.com/en-us/li...40(SQL.80).aspx) to
impersonate the user context for a SQL user account. However, this can only
be used by a sysadmin or db_owner and could not be used for Windows
accounts. In SQL Server 2005 you can use the EXECUTE AS clause to change the
execution context.

Here is a good article about EXECUTE AS:
http://blogs.msdn.com/sqlserverstor.../21/642061.aspx
HTH,

Plamen Ratchev
http://www.SQLStudio.comAwesome. Thanks for the quick response. I've got a lot of experience
in SQL in other languages, and some in SQL Server, but all the little
tricks of the trade I'm still short on.

Thanks again.

J

I''m not able to connect to my SQL server with SQLconnect

Hi,

I'm using the SQLconnect function in order to connect to my SQL server

I want to connect to a database called "CookieJar" without user name and password

this is the code:

#include "Container.h"

#include <windows.h>

#include <sqlext.h>

int main()

{

HENV hEnv = NULL; // Env Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Connection handle

HSTMT hStmt = NULL;// Statement handle

UCHAR szDSN[1024] = "CookieJar";// Data Source Name buffer

UCHAR szUID[10] = "";// User ID buffer

UCHAR szPasswd[10] = "";// Password buffer

UCHAR szModel[128];// Model buffer

SDWORD cbModel;// Model buffer bytes recieved

char buff[9] = "Testing";

UCHAR szSqlStr[128]= "INSERT into (Tablename) (ColumnName) Values ('Testing')" ;

RETCODE retcode;

//sprintf((char*)szSqlStr,"INSERT into (Tablename)(Columname) Values ('%s')",buff);

// Allocate memory for ODBC Environment handle

SQLAllocEnv (&hEnv);

// Allocate memory for the connection handle

SQLAllocConnect (hEnv, &hDBC);

// Connect to the data source "test" using userid and password.

retcode = SQLConnect (hDBC, (SQLWCHAR*)szDSN, SQL_NTS,/* (SQLWCHAR*)szUID*/ NULL, SQL_NTS, /*(SQLWCHAR*)szPasswd*/ NULL, SQL_NTS);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

// Allocate memory for the statement handle

retcode = SQLAllocStmt (hDBC, &hStmt);

// Prepare the SQL statement by assigning it to the statement handle

retcode = SQLPrepare (hStmt, (SQLWCHAR*)szSqlStr, sizeof (szSqlStr));

// Execute the SQL statement handle

retcode = SQLExecute (hStmt);

// Project only column 1 which is the models

SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

// Get row of data from the result set defined above in the statement

retcode = SQLFetch (hStmt);

// Free the allocated statement handle

SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource

SQLDisconnect (hDBC);

}

// Free the allocated connection handle

SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle

SQLFreeEnv (hEnv);

return 0;

}

The program works but it doesn't get into the "if" section...

And what do I need to put in szDSN - just the database name or the whole connection string?

please help,

Thanks,

Eli

Hi Eli,

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

note that the string you are using for database is actually the server name.

If you want to specify the database, call SQLSetConnectAttr() with SQL_ATTR_CURRENT_CATALOG.

Still not sure why you are sending NULL username and password. You may want to use 0 instead of SQL_NTS there, with NULL strings -- not sure if we ignore that or not.

Hope that helps,

John

I''m not able to connect to my SQL server with SQLconnect

Hi,

I'm using the SQLconnect function in order to connect to my SQL server

I want to connect to a database called "CookieJar" without user name and password

this is the code:

#include "Container.h"

#include <windows.h>

#include <sqlext.h>

int main()

{

HENV hEnv = NULL; // Env Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Connection handle

HSTMT hStmt = NULL;// Statement handle

UCHAR szDSN[1024] = "CookieJar";// Data Source Name buffer

UCHAR szUID[10] = "";// User ID buffer

UCHAR szPasswd[10] = "";// Password buffer

UCHAR szModel[128];// Model buffer

SDWORD cbModel;// Model buffer bytes recieved

char buff[9] = "Testing";

UCHAR szSqlStr[128]= "INSERT into (Tablename) (ColumnName) Values ('Testing')" ;

RETCODE retcode;

//sprintf((char*)szSqlStr,"INSERT into (Tablename)(Columname) Values ('%s')",buff);

// Allocate memory for ODBC Environment handle

SQLAllocEnv (&hEnv);

// Allocate memory for the connection handle

SQLAllocConnect (hEnv, &hDBC);

// Connect to the data source "test" using userid and password.

retcode = SQLConnect (hDBC, (SQLWCHAR*)szDSN, SQL_NTS,/* (SQLWCHAR*)szUID*/ NULL, SQL_NTS, /*(SQLWCHAR*)szPasswd*/ NULL, SQL_NTS);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

// Allocate memory for the statement handle

retcode = SQLAllocStmt (hDBC, &hStmt);

// Prepare the SQL statement by assigning it to the statement handle

retcode = SQLPrepare (hStmt, (SQLWCHAR*)szSqlStr, sizeof (szSqlStr));

// Execute the SQL statement handle

retcode = SQLExecute (hStmt);

// Project only column 1 which is the models

SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

// Get row of data from the result set defined above in the statement

retcode = SQLFetch (hStmt);

// Free the allocated statement handle

SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource

SQLDisconnect (hDBC);

}

// Free the allocated connection handle

SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle

SQLFreeEnv (hEnv);

return 0;

}

The program works but it doesn't get into the "if" section...

And what do I need to put in szDSN - just the database name or the whole connection string?

please help,

Thanks,

Eli

Hi Eli,

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

note that the string you are using for database is actually the server name.

If you want to specify the database, call SQLSetConnectAttr() with SQL_ATTR_CURRENT_CATALOG.

Still not sure why you are sending NULL username and password. You may want to use 0 instead of SQL_NTS there, with NULL strings -- not sure if we ignore that or not.

Hope that helps,

John

I'll try this question again...

When I run an RS report on server1 that connects to a sql database on server2
I get the error:
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. "
Suggestions?Jimbo,
Check the IIS settings, under the "Directory Security" tab on the website
properties.
Reeves|||A few possibilities. Are you calling the report on server1 from another
server. If so, then you might be seeing the double hop issue.
My guess is that your web site on Server1 is running anonymous AND you are
using windows security credentials to access the sql database source. In
report manager open up the data source. If you have Windows Integrated
Security checked that means RS will use the windows credentials of the
person running the report to access the data. If the website is in anonymous
mode then RS does not know who that user is. Also, all users will be quests,
nobody will have admin rights (including yourself) if the website is in
anonymous mode.
Two points, you cannot run the website in anonymous mode (unless you
perpetually take it out of anonymous mode anytime you need admin
priveleges). Second, my suggestion is to pick one of the other two
credentials options, have all users for a report use the same account to
access the data. I run SQL in mixed mode and have a SQL login just for
reporting that gives only readonly data access. This is better for
performance too since connection pooling will now work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:543C8DE5-5093-4545-BC0D-F25D78942D4E@.microsoft.com...
> When I run an RS report on server1 that connects to a sql database on
> server2
> I get the error:
> "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. "
>
> Suggestions?

IIS SQL Server connection error (urgent)

I keep getting this error when I try to connect to my SQL Server DB

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'Administrator'. Reason: Not associated with a trusted SQL Server connection.

when I installed SQL Server I set username and password to windows default which is Administrator and no password

the asp files are on IIS (localhost)

connstr = "Provider=SQLOLEDB;" &_
"Data Source=(local);" &_
"Initial Catalog=ShotWatcher;"&_
"User ID=Administrator;"&_
"Password="

shouldn't this work?

Please Help

JustinDo you have a SQL Server Standard account called Administrator? Or you're trying to use Windows account with the same name? If it's the latter, - remove User ID and Password attributes and replace them with Integrated Security=SSPI.|||And in connection string you should specify TRUSTED_CONNECTION=TRUE and try using servername instead of specifying local.

KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;306586) to more about it.|||i'll try that, thx

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?
The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>
|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
--
Ekrem Önsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>